Excel Grand Totals Formula of Percentages

0
votes

I have a question about how excel calculates grand total in a pivot table. My values are percentages so a grand total doesn't make sense. When I am connected to the analysis cube from excel i got this grand total automatically.But the result doesn't make any sense. I have two columns with values 100% and 31.48% and the grand total is 31.52%.

Does anyone know how this result is calculated?

2
excelpivot-tableolap
What Value Field Setting are you using for the pivot? If it's Sum of [Group name] it should give you a sum percentage. If you use Average of, it gives you a grand total as an average – David Andrei Ned
The category of the field is percentage – Digital_Jo4
Which excel version are you using? On my Excel 2010, if you right click the column name in the field list under Values there is "Sum", "Count", "Average", "Max", "Min", "Product", "CountNumbers", "StdDev", "StdDevp", "Var, and "Varp". No percentages – David Andrei Ned
Sorry i was talking about something else.I have the sum there – Digital_Jo4

2 Answers

0
votes

You can choose to remove grand totals from a pivot. As for %s, I did not manage to reproduce your results, I get Grand Totals of 272% (for rows of 85%,100%,87%) , 40% (0 , 0, 40) , 135% (46, 36, 53).

0
votes

The exact mechanism is proprietary information and there is not enough information in the OP to be sure what is being calculated as a percentage of what else but the general principle is that Excel's PivotTables work on aggregated values.

Thus where the percentage is A of A+B, the following would give the results mentioned for two columns: 

  A        B       
   100        0    100.00%  
53,831  117,169     31.48%

and the total percentage [calculated as: 100*Sum(A)/Sum(A+B) ] be the result mentioned for Grand Total: 

53,931  117,169     31.52%

In effect weighting has been applied. This behaviour is consistent with other PT display options.

As described for Calculated Fields at Contextures:

the individual amounts in the other fields are summed, and then the calculation is performed on the total amount.