CODEWITHSUNDEEP
powerpivotdax
LoganTheSnowEater
LoganTheSnowEater

Reputation: 575

DAX Measure with different granularities

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

Tables: Data

Model:

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)))

enter image description here enter image description here

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?

Upvotes: 0

Views: 1059

Answers (1)

Kyle Hale
Kyle Hale

Reputation: 8120

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.

Upvotes: 1

Related Questions