Forum Discussion
Correctly aggregate in matrix
Hi everyone,
I have been trying this for a while now and would really appreciate your help.
I am trying to calculate the effect of price increases for individual items per sales organization.
'Sales Price 19' and 'Sales Price 21' are measures calculated from the tables Net sales and Quantity.
The problem that I am having is that the pricing increase on each product does not add up to the total above.
Since not all items were sold by the same organization in both years, there are NaN values in the matrix.
I only want the pricing effect to be calculated if 'Sales Price 21' and 'Sales Price 19' are both >0. The pricing effect for sales org. 1 should be 1,69.
I have uploaded the dataset:
https://1drv.ms/u/s!AgksFfPmuAXgat3p1AIkxESCK1E?e=ZXGlGe
I'm kinda new with this, so please excuse if the solution is very obvious 😉
Thanks!
HI Still_learning,
It sounds like a common measure total level calculated issue of multiple aggregations, you can take a look at Greg's blog if it suitable for your scenario:
Measure Totals, The Final WordRegards,
Xiaoxin Sheng
3 Replies
- amitchandak
Super User
Still_learning , try a measure like example
sumx(values(Table[sales org]), calculate(if( [Sales Price 21]> 0 && [Sales Price 19] >0,[Sales Price 21] -[Sales Price 19], blank())))
amitchandak , thx for your help.
Unfortunately your measure does have the same result as my previous attempt.P.S. I forgot to multiply the pricing difference with the quantity in my earlier screenshot so I changed that one
HI Still_learning,
It sounds like a common measure total level calculated issue of multiple aggregations, you can take a look at Greg's blog if it suitable for your scenario:
Measure Totals, The Final WordRegards,
Xiaoxin Sheng
