# 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 Thursday, October 30, 2014 1:55 PM
Thursday, October 30, 2014 1:54 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,

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

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

### All replies

• Thursday, October 30, 2014 2:10 PM
• 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,

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

• Marked as answer by Thursday, October 30, 2014 2:41 PM
• Edited by 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,