locked
Calculate percrentage based on 2 column and one filter RRS feed

  • Question

  • Hi,

    I am looking for a way to compute percentage based one two column and 1 filter but i have a problem when every thing is checked on my filter.

    The table as follow is my example:


    ID cat1 cat2 number total pct
    1 a aa 5 40 13%
    1 a ab 20 40 50%
    1 a ac 15 40 38%
    1 b ba 12 47 26%
    1 b bb 20 47 43%
    1 b bc 15 47 32%
    2 a aa 9 45 20%
    2 a ab 16 45 36%
    2 a ac 20 45 44%
    2 b ba 10 50 20%
    2 b bb 20 50 40%
    2 b bc 20 50 40%

    I create a power table as follow:

    Filter: column ID

    Axis: cat1 and cat2

    values: pct

    The problem is when I want to select all my data in my filter, a sum/mean is done for pct but the results are wrong. Did I missed something ? Is there a way to correct the problem ?


    • Edited by mi jo Thursday, October 30, 2014 1:55 PM
    Thursday, October 30, 2014 1:54 PM

Answers

  • Hi mi jo,

    It appears that you have created your percentage as a Calculated Column and what you actually want is a Calculated Field.

    You can create a Calculated Field by clicking on the Power Pivot ribbon and using a DAX formula similar to the following:

    Percentage:=DIVIDE(SUM(TableName[number]), SUM(TableName[total]))


    I have formatted this Calculated Field as a percentage and used it to create a Pivot Table:

    Is this what you were after or have I misunderstood the problem?

    Edit: Just took a quick look at the workbook and can see that the pct column is coming in as a fixed value for a row. The above solution should help. A best practice would be to first create a Calculated Field for each of the number and total columns such as...

    Sum of Number:=Sum(TableName[number])


    And...

    Sum of Total:=Sum(TableName[total])


    And then finally update this DAX formula:

    Percentage:=DIVIDE(SUM(TableName[number]), SUM(TableName[total]))
     

    To this...

    Percentage:=DIVIDE([Sum of Number], [Sum of Total])


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    • Marked as answer by mi jo Thursday, October 30, 2014 2:41 PM
    • Edited by Michael Amadi Thursday, October 30, 2014 2:49 PM Minor tweaks
    Thursday, October 30, 2014 2:19 PM

All replies

  • Could you give a link to download your powerpivot?
    Thursday, October 30, 2014 2:10 PM
  • Yes I can, you would need to download it i suppose.

    http://1drv.ms/1ziJ62R

    Thursday, October 30, 2014 2:14 PM
  • Hi mi jo,

    It appears that you have created your percentage as a Calculated Column and what you actually want is a Calculated Field.

    You can create a Calculated Field by clicking on the Power Pivot ribbon and using a DAX formula similar to the following:

    Percentage:=DIVIDE(SUM(TableName[number]), SUM(TableName[total]))


    I have formatted this Calculated Field as a percentage and used it to create a Pivot Table:

    Is this what you were after or have I misunderstood the problem?

    Edit: Just took a quick look at the workbook and can see that the pct column is coming in as a fixed value for a row. The above solution should help. A best practice would be to first create a Calculated Field for each of the number and total columns such as...

    Sum of Number:=Sum(TableName[number])


    And...

    Sum of Total:=Sum(TableName[total])


    And then finally update this DAX formula:

    Percentage:=DIVIDE(SUM(TableName[number]), SUM(TableName[total]))
     

    To this...

    Percentage:=DIVIDE([Sum of Number], [Sum of Total])


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    • Marked as answer by mi jo Thursday, October 30, 2014 2:41 PM
    • Edited by Michael Amadi Thursday, October 30, 2014 2:49 PM Minor tweaks
    Thursday, October 30, 2014 2:19 PM
  • In fact every thing is fixed :D Thanks a lot.
    Thursday, October 30, 2014 2:41 PM
  • No problem :)

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    Thursday, October 30, 2014 3:36 PM