Answered by:
Calculate percrentage based on 2 column and one filter
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: @nimblelearnThursday, October 30, 2014 3:36 PM