0

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?

LoganTheSnowEater
  • 555
  • 2
  • 4
  • 17
  • 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 Jan 28 '16 at 02:49
  • 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 Jan 28 '16 at 02:50
  • 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 Jan 28 '16 at 02:50
  • Or to put it another way, Total Amount 3 would be fine if the grand total was 120? – Kyle Hale Jan 28 '16 at 02:50
  • 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 Jan 28 '16 at 11:34

1 Answers1

1

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.

Kyle Hale
  • 7,912
  • 1
  • 37
  • 58
  • Thanks Kyle. Yes, I think that works; I have always tried to avoid putting bridge tables onto rows/columns of PivotTables. If there were several columns of attributes on both of the one-side tables (Pri and Ptr), I wouldn't be able to put those on too if using the bridge table. It's one of the drawbacks of lack of native M2M support in PowerPivot. Thanks for the help! – LoganTheSnowEater Jan 28 '16 at 11:33
  • Well your real problem is that the values in Pri and Ptr don't act as filters on Bridge, so using Pri[Pri], ALL(Ptr[Ptr]) will return (as you see) a row for each Pri-Ptr combo, not just Bridge combos. – Kyle Hale Jan 28 '16 at 15:08
  • Maybe this was easier than I thought, but your calculations helped me get there! If I use FinalTotalAmt:=CALCULATE([Total Amt All],Bridge), I get the right results while using Pri and Ptr on the PivotTable (and not having to put the Bridge columns on the PivotTable). Seems like the best of both worlds. – LoganTheSnowEater Jan 29 '16 at 13:21