Answered by:
Using Percent Column in Power Pivot Table
Question

Hello. I have a power pivot table with the following value columns: Actual AmountBudget AmountVariance. 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
Answers

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+21001) / (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 Charlie Liao Monday, February 22, 2016 8:51 AM
 Marked as answer by Charlie Liao Tuesday, February 23, 2016 8:13 AM
Monday, February 8, 2016 7:47 AMAnswerer
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]))
Reference
https://msdn.microsoft.com/enus/library/ee634825.aspx?f=255&MSPPError=2147217396
https://msdn.microsoft.com/enus/library/ee634795.aspxIf this is not what you want, please provide us some sample data, so that we can make further analysis.
Regards,
Charlie Liao
TechNet Community SupportFriday, 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 AMAnswerer 
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+21001) / (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 Charlie Liao Monday, February 22, 2016 8:51 AM
 Marked as answer by Charlie Liao Tuesday, February 23, 2016 8:13 AM
Monday, February 8, 2016 7:47 AMAnswerer