Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Still_learning
Frequent Visitor

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.

Still_learning_0-1623072565364.png

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!

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
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.


Still_learning_0-1623082318950.png

P.S. I forgot to multiply the pricing difference with the quantity in my earlier screenshot so I changed that one

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors