locked
Using Percent Column in Power Pivot Table RRS feed

  • 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

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+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 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 AM
    Answerer

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/en-us/library/ee634825.aspx?f=255&MSPPError=-2147217396
    https://msdn.microsoft.com/en-us/library/ee634795.aspx

    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
    Answerer
  • 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 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 AM
    Answerer