DAX Measure with different granularities

Question

I'm looking for help with how to write a specific DAX measure. Here is a simplified version of my data and model:

Tables:

Model:

Measures:

Total Amt:=SUM(Amounts[Amt])
Total Pos Amt:=SUMX(Amounts, IF([Amt]<0,0,[Amt]))
Total Amt All:=CALCULATE([Total Pos Amt],ALL(Ptr))
Total Amt All 2:=SUMX(Bridge,CALCULATE([Total Pos Amt],ALL(Ptr)))
Total Amt All 3:=SUMX(VALUES(Bridge[Pri]),CALCULATE([Total Pos Amt],ALL(Ptr)))

As you can see in the first PivotTable (where [Pri] & [Ptr] are row fields), the highlighted cells show values with an issue. The [Total Pos Amt] measure sums up the [Amt] column in the Amounts table by iterating through it and evaluating an expression where negative amounts are treated as zero and positive amounts are kept. At a [Pri] level granularity, I want that same logic to apply (i.e. evaluate the expression at a [Ptr] level). The problem with the [Total Amt All] measure is that on the PivotTable I get a row for [Ptr] Z under [Pri] A which I don't want. Measures [Total Amt All 2] and [Total Amt All 3] solve that issue but the subtotals at a [Pri] level are wrong in [Total Amt All 2] and the grand total is wrong in [Total Amt All 3].

Any help would be greatly appreciated! How can I write a measure that won't show a [Ptr] that is not associated with a [Pri] per the Bridge table, but that also correctly sums up the [Total Pos Amt] measure at a [Pri] level?

Some good future advice: explain what you want in terms of the business solution, not a bunch of specific measures and subtotals, it's very hard to follow exactly what you want. — Kyle Hale
Specifically you want to see every Ptr associated with a Pri that has a total amount, even if it's not in the Amounts table? — Kyle Hale
So for example A-GP has no value in Amounts, but you want to see a row in your PivotTable that says A-GP-20? — Kyle Hale
Or to put it another way, Total Amount 3 would be fine if the grand total was 120? — Kyle Hale
Sorry for the confusing example, I agree I could have done a better job explaining what I was trying to do. But you nailed it. — LoganTheSnowEater

Kyle Hale Kyle Hale · Accepted Answer · 2016-01-28T03:04:44

So one of your problems might be which fields you're using in your PivotTable. I got it work by using your bridge table as the fields:

TotalAmtBridged:=CALCULATE ( SUMX(Amounts, IF([Amt]<0,0,[Amt]) ) , Bridge )
FinalTotalAmt:= Calculate([TotalAmtBridged], ALL(Bridge[Ptr])

And then the PivotTable uses Bridge[Pri] and Bridge[Ptr]. So TotalAmtBridged just forces your total amount to use the Bridge context, and then FinalTotal says ignore Ptr (i.e. for each row we're displaying figure out the total amount for Bridge[Pri] only).

And then the grand total's already doing that, so Bob's your uncle.

DAX Measure with different granularities

1 Answers