How to get the grand total of a if(,,) calculation that I want

[Anonymous]

Hi, recently encountered this issue, wonder if anyone can help:

This is a simplified example. 

Say, C = if(A-B<0,0, A-B). If I drag C into the table, hierarchy 2 is always correct, but hierarchy 1 is not.

If I just drag C in to a new card to let it show sum of C, it's also incorrect. Just like in Hierarchy 1, it'll be showing 0.

If I want to calculate everything at the granularity level of Hierarchy 2, then aggregate up, say, at Hierarchy 1, each row is showing 20, and at the grand total level, it's showing 40, how should I write the formula for C?

Thank you so much in advance!

[tamerj1]

Hi @Anonymous 

in general you can simulate the filter context by virtually creating the report's table with dax code, then you can sum the visible values by iterating over this table. Something like 

SUMX (

SUMMARIZE ( Table1, Table1[Hierarchy1], Table1[Hierarchy1] ),

CALCULATE ( if(A-B<0,0, A-B) )

)

[daXtreme]

@Anonymous 

How you write such queries depends heavily on the underlying structure of your model. One cannot give you an answer without knowing these details. For instance, the formula would depend on whether or not you have an id in the table that uniquely identifies any combination of hierarchy1-hierarchy2. If you don't have such an index, then one might need to employ the KEEPFILTERS function... It all depends.