Home Forums Power Pivot How to add a basic variance column in powerpivot

This topic contains 3 replies, has 2 voices, and was last updated by  tomallan 1 month ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #9596

    wf
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Hi,

    I just started working with powerpivot, drawn by the “power” element which truly looks like a great DIY BI to me.  Thanks to many others struggle (and especially their posts about it) I overcame some obstacles and managed to set up a data model sourcing on MS SQL.

    However getting into the practical side I ran into a problem I find little about (so perhaps it is mere ignorance)

    I would like to know if a variance can be presented in powerpivot, just like it can be don in a simple excel pivot via a calculated item (Var = ACTUAL-BUDGET).
    In powerpivot, calculated items are not an option I had a weak attempt at creating a measure to do the job:
    Var=CALCULATE(Sum(Table1[Value]), Table1[Category]=”ACTUAL”) -CALCULATE(sum(Table1[Value]), Table1[Category]=”BUDGET”)

    To explain myself, I enclosed a simple dataset, the excel pivot and powerpivot

    When presented in the pivot as both the “Var” measure and the data series need to be presented the variance is duplicated.

    Is there a simply way to obtain the same result as with an calculated item?

    Thanks in advance

    Kr,

     

    Wf

     

     

    Attachments:
    You must be logged in to view attached files.
    #9598

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2244
    • Total: 2253

    Kr,

    I think the issue here is that you are new to new to Power Pivot and need to know that you have accidentally painted yourself into a corner vis-à-vis filter context (one of Power Pivot’s most enabling features, once learned). Here is a link to a page with an excellent and free 8-page “reference card” download that will further you along the right path.

    Here is a concept that Rob Collie teaches in his online Power Pivot course: Portable formulas.

    Portable formulas are a pattern for formula re-use: define a formula once and reuse it in any or all pivots in the same workbook. Portable formulas also take advantage of centralized formatting inside the Power Pivot window where you declare the format of a measure (calculated field) once, and that format will hold for all pivots in the same workbook.

    Another benefit of portable formulas is they can almost always be used as building blocks to create other formulas. For example:

    Total Value:=SUM ( Table1[Value] )
    Total Budget:=CALCULATE([Total Value], Table1[Category] = “BUDGET”)
    Total Actual:=CALCULATE( [Total Value], Table1[Category] = “ACTUAL”)
    Variance:=[Total Actual] – [Total Budget]

    Please see attached Excel 2016 workbook with a pivot that does not generate the “duplicate”.

    Tom

    Attachments:
    You must be logged in to view attached files.
    #9617

    wf
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Tom,

    Thanks for your swift response, I now managed to apply it to my large data set as intended.

    Wim

    #9618

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2244
    • Total: 2253

    Wim,

    Glad to hear of your success!

    Best regards,

    Tom

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.