Skip to Content

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

/wp-content/uploads/2015/10/query_809403.png

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

/wp-content/uploads/2015/10/report_809404.png

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.

Regards,

Rogerio

2 Comments
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:

    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.

    NMG

    • 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 !!!

      Regards,

      Rogerio