View Full Version : Drill up and Drill Down - Power Pivot

2020-05-01, 09:40 AM

I am facing problem in doing drill up and drill down in power pivot. i have the values in percentage and when i do the hierarchy for few data and export the data into pivot table in power pivot and percentage are showing up in counts and when i change the count into sum or average i am getting error has - we can t summarize this field with sum because it's not a supported calculation for text data types.

i have given the data in the attachment. Could you please help me with the Drill up/down - i need it in percentage which i am getting in count after doing the pivot table.

Bob Phillips
2020-05-01, 04:03 PM
It is probably because of the formula in the percentage column, where you set the error value to "". PowerPivot will look at the column and see mixed data types, not a pure number. I set the error value to 0 and I could change the aggregation type to sum.

2020-05-02, 01:54 PM
Hi Bob,

I agree for your statement. but when you give 0 the average of the cells would be different than the average of the cells having in blank.
is there any alternative way that even if the cells are blank and pivot table to come as average or sum for the percentage column?

2020-05-03, 05:55 PM
You can add measures within Power Pivot to do the calculations. See the attached for an example of this, with the caveat that others with more experience may be able to provide clearer, more concise and/or more robust formulas.

2020-05-11, 10:38 AM
Thank you all for your Valuable Inputs :)