Search code examples
excelaggregationpowerpivotdax

Summing average aggregation in grand total (DAX)

I created a measure in PowerPivot that has the following formula: Calculated Percentage:=[PercentageA]+[PercentageB]*AVERAGE([Multiplier])

Here is the result:

Pivot

What I would like from this measure is in the Desired values column. The point would be to see the grand total as the SUM of the values of the measure instead of multiplying the grand total PercentageB with the grand total average of Multiplier.

Solution

  • One way to solve this would be to use SUMX function. Which based on a provided table or column does an iterative calculation based on a formula provided, and then sums the result.

    So in the below example, the VALUES( table_Name[Row Labels]) is used to create a table of unique values from the Row Lables column what it will iterate on. Then within each row grouping it will apply the defined formula.

    Measure:=  SUMX( VALUES( table_Name[Row Labels]), 
                Calculate([PercentageA]+[PercentageB]*AVERAGE([Multiplier])))

    Note: Depending on how your average([Multiplier]) is defined, you may need to use calculate to override the context. E.g. if it is suppose to be the average over the entire set instead of the row.