 # 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

### Assigned tags

You must be Logged on to comment or reply to a post. ...or to express in a different way:

=Average([Sales revenue] ForEach([Year])/[Quantity sold] ForEach([Year]))

e.g. average unit price per year.

This appears to illustrate a difference in the calculation engines between 3.x and 4.x: I don't have 4.x to test, but it appears that 4.x favours the latter calculation by default, 3.x the former.

NMG Former Member
Blog Post Author

Hi Neil,

thanls for your comment. The reason I wrote it is that here, at my company, people are not aware of situations like that.So I thought it might be of interest.