Possible Pitfall when multiplying/dividing measures and how to avoid it

Hello everyone,

I´m sorry if you all are aware of it, but I think it deserves some attention :

This is my query over e-fashion

What I´ll do here is calculating  the unitary price for a product in a Line in the dumbiest way :

Just dividing [Sales revenue] by [Quantity sold] for each [Year]. This gives me the following table

So far so good …

But what if I took [Year] away from my table ?

Here´s what happens

As you can see, the values are different but why this happens ?

BO adds another rule of precedence when calculating values, besides the * / + – precedence , there is the default aggregation of the measure that is with greater precedence.

So, when calculating =[Sales revenue]/[Quantity sold], what is really been calculated is =Sum([Sales revenue])/Sum([Quantity sold]).

In order to avoid this problem, you better add back the missing dimmension (in this case, [Year]) through the use of the ForEach operator

=Sum( ([Sales revenue]/[Quantity sold]) ForEach ([Year]))/3. Like in the table below

So when dealing with multiplication/division of Measures, always remember  to use ForEach operator.

Regards,

Rogerio