Skip to Content
Author's profile photo Former Member

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

report def calc.png

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

report full.png

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



Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Neil Mitchell-Goodson
      Neil Mitchell-Goodson

      ...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:

      sapn 141015.JPG

      I don't have 4.x to test, but it appears that 4.x favours the latter calculation by default, 3.x the former.


      Author's profile photo Former Member
      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.

      Thanks for your comment !!!