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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Measure not filtered by other column

Hey all,

 

I hope I can explain this problem through an Excel, since the data model is super advanced, and will be too difficult to clean of the sensitive data.

 

I've explained all the steps I want to do in the Excel: https://we.tl/t-RbfCdR0HRw 

 

So basically, I want to show a table, where the calculation of the sum of 'Amount of Stores' is shown per product. I have a calculation that works now when I have a table where in the columns I have 'Store Type', by doing 'Distribution' * 'Total stores' (per type). But if I delete 'Store Type' from the Column view, then the calculation doesn't split the sum up by Store Type anymore.

So I guess I am looking for a way to keep that calculation split up per Store Type, but eventually show the sum of the results.

 

I hope the Excel makes it clear what I am trying to achieve. I would include the relations too, but there's a bit too many involved for now. Maybe if you guys can't help me out from this, I'll see if I can add it somewhere.

 

Thanks a lot!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@Anonymous 

 

If you need a calculation that's always performed separately for each Store Type and then summed up... well, then you have to do just that - use SUMX to calculate the partial results for each visible Store Type in the current context and then sum them up.

 

Something like:

 

SUMX( DISTINCT( T[Store Type] ), [(LAST BIT) Your Measure or Expression Here] )

 

remembering that you need to surround the LAST BIT with CALCULATE if you have an expression (since measures are ALWAYS implicitly wrapped up in CALCULATE, you don't need to do it with them). This is because you have to force context transition.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Thanks a lot! 

This worked like a charm. I knew how the calculation had to be performed, the issue was not knowing which correct DAX to use. I'd been looking around and trying, but couldn't find a solution, until now!

Anonymous
Not applicable

@Anonymous 

 

If you need a calculation that's always performed separately for each Store Type and then summed up... well, then you have to do just that - use SUMX to calculate the partial results for each visible Store Type in the current context and then sum them up.

 

Something like:

 

SUMX( DISTINCT( T[Store Type] ), [(LAST BIT) Your Measure or Expression Here] )

 

remembering that you need to surround the LAST BIT with CALCULATE if you have an expression (since measures are ALWAYS implicitly wrapped up in CALCULATE, you don't need to do it with them). This is because you have to force context transition.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors