0

This should be easy but I must be missing something obvious. I have the following PowerPivot table:

Level 1    Level 2    Amount
-------    -------    ------
A          X          100
A          Y          200
B          X          400
B          Y          600

I'm trying to build a measure (not a calculated column) that iterates over each item in Level 2 and divides the amount by the subtotal of all amounts for Level 1 equal to the current Level 1 amount.

In other words, I want the measure to do this

Level 1    Level 2    Measure
-------    -------    ------
A          X          100 / 300
A          Y          200 / 300
B          X          400 / 1,000
B          Y          600 / 1,000

I have tried this:

AmountSum:=SUM(Table1[Amount])
Measure:= SUMX(Table1, 
    [AmountSum]/CALCULATE(SUMX(Table1, [AmountSum]), ALL(Table1[Level 2])))

This and other iterations I've tried always end up showing 1 for each calc meaning that (or so it appears) only the initial Level 2 row is in scope when calc'ing the denominator, despite my use of ALL.

Thanks in advance for any advice people can offer!

Edit: Assume that the [AmountSum] logic is dependent on and needs to be calculated at the row level for the Level 2 items. It was simplified in the example above, but in reality it is dependent on the Level 2 data and at a Level 1 level should only be adding up results calculated at a Level 2 level.

LoganTheSnowEater
  • 555
  • 2
  • 4
  • 17
  • Good description of the calc requirements. Should it show a subtotal or grand total? – GregGalloway Jan 26 '16 at 13:28
  • Let's say in a PivotTable I will have Level 1 and Level 2 as row fields (in that order), and I would be showing Subtotals for Level 1. Therefore the measure result for each Level 1 subtotal should be 1 (or 100%). – LoganTheSnowEater Jan 26 '16 at 14:53

1 Answers1

2

Try this:

AmountSum:=SUM(Table1[Amount])
Measure:= DIVIDE([AmountSum], CALCULATE([AmountSum], ALL(Table1[Level 2])))

If it has to be calculated at the leaf level:

AmountSum:=SUMX(Table1, <YourMoreComplexCalcHere>)
Measure:= DIVIDE([AmountSum], CALCULATE([AmountSum], ALL(Table1[Level 2])))
GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • Greg, I should have explained that I need to use a SUMX because the calculation always needs to be done at an individual row level for Level 2 items (the calc explained above is simplified). But assume that the [AmountSum] logic that is dependent on each row and at a Level 1 level it should only be adding up the results calculated at a Level 2 level. – LoganTheSnowEater Jan 26 '16 at 15:10
  • So replace his SUM()s with SUMX()s, the important part is how he uses CALCULATE() and ALL(). – greggyb Jan 26 '16 at 15:13
  • @LoganTheSnowEater why does the grand total need to be calculated at the row level? Can you explain further? I believe my calc will produce the results you want but would like to hear details. – GregGalloway Jan 26 '16 at 15:20
  • @LoganTheSnowEater I edited my answer to include a SUMX version and to reuse the AmountSum measure. Would still like to hear the business need around calculating at the row level – GregGalloway Jan 26 '16 at 15:40