Results 1 to 5 of 5

Thread: Drill up and Drill Down - Power Pivot

  1. #1
    Neophyte Kle05's Avatar
    Join Date
    May 2020
    Posts
    4
    Articles
    0
    Excel Version
    2016

    Drill up and Drill Down - Power Pivot



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    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.

  3. #3
    Neophyte Kle05's Avatar
    Join Date
    May 2020
    Posts
    4
    Articles
    0
    Excel Version
    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?

  4. #4
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    119
    Articles
    0
    Excel Version
    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.
    Attached Files Attached Files

  5. #5
    Neophyte Kle05's Avatar
    Join Date
    May 2020
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Thank you all for your Valuable Inputs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •