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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.