Measure not filtered by other column

[Anonymous]

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!

[Anonymous · Accepted solution]

@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.

[Anonymous]

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!