Correctly aggregate in matrix

[Still_learning]

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!

[v-shex-msft · Accepted solution]

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 Word 

Regards,

Xiaoxin Sheng

[amitchandak]

@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())))

[Still_learning]

@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