Drill up and Drill Down - Power Pivot

Kle05

New member
Joined
May 1, 2020
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2016
Hi,

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.
 

Attachments

  • PE - Data.xlsx
    147.8 KB · Views: 15

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 

Kle05

New member
Joined
May 1, 2020
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2016
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?
 

NormS

New member
Joined
Jul 30, 2017
Messages
122
Reaction score
0
Points
0
Excel Version(s)
Excel 2016 ProPlus
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.
 

Attachments

  • PE - Data with Measures.xlsx
    169.4 KB · Views: 16

Kle05

New member
Joined
May 1, 2020
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2016
Thank you all for your Valuable Inputs :)
 
Top