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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 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.

October NL Carousel

Fabric Community Update - October 2024

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