Skip to Content

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

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

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

    NMG

    (0) 
    1. Rogerio Plank 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 !!!

      Regards,

      Rogerio

      (0) 

Leave a Reply