# Using Percent Column in Power Pivot Table

• ### Question

• Hello.  I have a power pivot table with the following value columns: Actual Amount|Budget Amount|Variance. I need to add a column that will return a variance % for each row, including the subtotal row as a % and not a summed total.  Is this possible?

Thank you!

Thursday, February 4, 2016 5:27 PM

• You really should not want to do this with a calculated column. An example, suppose you have just two rows:

Actual - Budget - Variance

102 - 100 - 2

2 - 1 - 1

In a Variance% column, the value would be 2% for row 1, and 50% for row 2. Now in a pivot table, what should be on the total row? It's not 52%, nor 26%, nor 2%, nor 50% - in short, none of the basic aggregations will lead to the correct result. The only way to do this is to calculate (102+2-100-1) / (100+1) = 2.97%. Calculated columns will not do this for you, calculated fields will.

(BTW, the correct formula for my earlier reply is Variance%:=DIVIDE([Actual Amount]-[Budget Amount],[Budget Amount]), obviously.)

• Proposed as answer by Monday, February 22, 2016 8:51 AM
• Marked as answer by Tuesday, February 23, 2016 8:13 AM
Monday, February 8, 2016 7:47 AM

### All replies

• Hi Jelly,

According to your description, you need to calculate the percentage for variance of subtotal variance, right?

I have tested it on my local environment, we can use the expression below.
=Test[Variance]/CALCULATE(SUM(Test[Variance]),ALLEXCEPT(Test,Test[Type]))

If this is not what you want, please provide us some sample data, so that we can make further analysis.

Regards,

Charlie Liao
TechNet Community Support

Friday, February 5, 2016 2:12 AM
• From your description, it seems you are not talking about a table in a Power Pivot model (with calculated columns) but a pivot table based on a Power Pivot model.

In this case, returning the variance as a percentage is straightforward. Keep in mind that the subtotal and total rows in a pivot table do not sum the rows above them, but calculate a measure on the combination of all the rows above them. So if you have measures [Actual Amount] and [Budget Amount], the variance percentage is

Variance%:=DIVIDE([Actual Amount],[Budget Amount])

formatted as a percentage. This will work on subtotals just like other rows.

Friday, February 5, 2016 7:24 AM
• Thank you for your reply!  I want to add the column to the model and then place it in the values section of the pivot table.  I can do a calculated field in the model that works, IFERROR([Variance]/[BudgetAmount],[ActualsAmount]*.01), but it doesn't work when i place it in the pivot table.  In the model, every account has an entry for each month and is assigned to a specific category.  In the pivot table, there is a filter to choose the month you want to view, and the accounts are compiled by category.  There is a subtotal row for each category that sums the total for each category.  So, say acct 01600 shows 100% for all 12 months in the model.  When I filter for 1/31/16 in the pivot table, it shows the calculation as 1200% instead of 100%.  I tried to attach pics of the model and pivot table but get an error stating that i can't because my account has not been validated.  I am new to Power Pivot so I truly appreciate your help!

Friday, February 5, 2016 4:54 PM
• You really should not want to do this with a calculated column. An example, suppose you have just two rows:

Actual - Budget - Variance

102 - 100 - 2

2 - 1 - 1

In a Variance% column, the value would be 2% for row 1, and 50% for row 2. Now in a pivot table, what should be on the total row? It's not 52%, nor 26%, nor 2%, nor 50% - in short, none of the basic aggregations will lead to the correct result. The only way to do this is to calculate (102+2-100-1) / (100+1) = 2.97%. Calculated columns will not do this for you, calculated fields will.

(BTW, the correct formula for my earlier reply is Variance%:=DIVIDE([Actual Amount]-[Budget Amount],[Budget Amount]), obviously.)

• Proposed as answer by Monday, February 22, 2016 8:51 AM
• Marked as answer by Tuesday, February 23, 2016 8:13 AM
Monday, February 8, 2016 7:47 AM