PowerPivot Grand Total, Sum of all change in %s?

0
votes

So I am working on a report that has to show total 'pts' in a table based on the performance of certain groups of work.

Right now I have a PowerPivot that Sums Actuals, Forecasts, and the Variance. Then I have separate formulas that filter on each group so that I get a variance % based on each group.

Example formula - 'Group1 Acc:=ABS(CALCULATE(([Actual]-[Forecast])/[Actual],talbe[name]="Group 1"))'

I want the table to look like this....

Column      Group 1 Group 2
Criteria 1  25%     25%
Criteria 3  20%     20%
Criteria 4  10%     10%
Grand Total 21.2%   55%

Group 1 is how it is currently working and makes sense to me but isn't doing what I want. It is getting the weighted % change of all the groups. Total Actual-Total Forecast/Total Forecast basically. In this example it comes out to 21.2% because Criteria 4 had a larger % of the total Actual and Forecast for the time period I am comparing.

I want it to work like it is showing in Group 2. The SUM of the total %s over each criteria group. I've tried SUMX formulas, Value(), and such but can't figure this out. Any help would be appreciated.

Thank you!

2
excelexcel-formulapivot-tabledaxpowerpivot
Are you importing percentages as the raw data, or creating them using calculated fields? – Chris
Creating them using calculated fields. – Tfo
This behavior makes sense. The grand total at the bottom is a calculated field as well. Therefore, using the Grand Total for what you want, just will not work. Your best bet is to put this Grand Total figure somewhere else, and simply SUM the column. Sorry if this isn't the answer you were after, but i just dont see another way... – Chris

2 Answers

0
votes

Check out this article: Subtotals and Grand Totals That Add Up “Correctly”

Basically, you can create logic so that the total behaves separately from the individual rows. Something along these lines:

Measure = 
IF(COUNTROWS(VALUES(Table1[Criteria])) = 1,
   [Group 1 Acc],
   SUMX(VALUES(Table1[Criteria]), [Group 1 Acc])
)
-1
votes

Data:

Type Actual Forcast

Criteria1 33 32

Criteria2 543 345

Criteria3 643 633

Criteria4 45 65

Criteria5 7890 7999

MEASURE 'datatable'[SumOfActuals] =
    SUM ( 'DataTable'[Actual] )
MEASURE 'datatable'[SumOfForcast] =
    SUM ( 'DataTable'[Forcast] )
MEASURE 'datatable'[Group1] =
    DIVIDE (
        'datatable'[SumOfActuals],
        CALCULATE ( 'datatable'[SumOfForcast], ALLSELECTED () )

In Excel, filter for Criteria 3 and 4

Row Labels Group1

Criteria3 92.12 %

Criteria4 6.45 %

Grand Total 98.57 %