Possible Pitfall when multiplying/dividing measures and how to avoid it
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.