Variance Column in Pivot Table

[SimonNU]

Hi Guys

I'm sure you get this question a lot from newbies but, given my circumstances, how can I create a pivot table with Variance and Variance % columns?

I'd like a way to keep my column label (Actual, Forecast, Plan & Last Year) but I don't know how to write a measure which refers to each in order to calculate variances between them. One method I made involved manually creating all of my columns (which allows me to refer to them) using the measures below, but surely this isn't best practice?

ACTUALS:=CALCULATE(-SUM('COMBINED GL DATA'[CURRENT PERIOD]),'COMBINED GL DATA'[SOURCE]="CURRENT YEAR ACTUALS")

PLAN:=CALCULATE(-SUM('COMBINED GL DATA'[CURRENT PERIOD]),'COMBINED GL DATA'[SOURCE]="CURRENT YEAR PLAN")

FORECAST:=CALCULATE(-SUM('COMBINED GL DATA'[CURRENT PERIOD]),'COMBINED GL DATA'[SOURCE]="CURRENT YEAR FORECAST")

LAST YEAR:=CALCULATE(-SUM('COMBINED GL DATA'[CURRENT PERIOD]),'COMBINED GL DATA'[SOURCE]="LAST YEAR ACTUALS")

FORECAST VAR:=[ACTUALS]-[FORECAST]

PLAN VAR:=[ACTUALS]-[PLAN]

LY VAR:=[ACTUALS]-[LAST YEAR]

BLANK:=blank()

FC VAR %:=IFERROR([FORECAST VAR]/[ACTUALS],BLANK())

PLAN VAR (%):=IFERROR([PLAN VAR]/[ACTUALS],BLANK())

LY VAR (%):=IFERROR([LY VAR]/[ACTUALS],BLANK())

I've only been using PowerPivot for a week so bear with me... If you require any more info, e.g. my table layout or a better explanation (I found it difficult to word this), please ask!

 

[scottsen]

I kind of love you

* I don't really understand the - in front of your SUMs, but I suspect your data is just that way.
* Instead of IFERROR on the divide, there is a new'ish function DIVIDE() that does the same thing, but looks prettyier and performs better
* I have no idea why you have a measure named [BLANK]

Otherwise your measures look EXACTLY how I would write them, and your model (all data in a single column, w/ a type/source column) is REALLY GOOD for power pivot. Will perform well.

It's great work, continue on.

 

[Tianbas]

This really looks good for one week of PowerPivot, much better than my measures after my first week. As Scott says its exactly as it should look like if you want to show them all in one table.

I had a very similar table in the beginning so here an idea for your second week

Having all of the measures in one table could be too much numbers for a manager to understand at once and sometimes you have manager that want to see the the one variance that is not covered by all of your measures (e.g. comparison to last quarter). What I did is making a variance report that has 3 columns only. The first 2 columns show a measure the 3rd the variance. Which measure (Actual/forecast/plan) is shown in the first 2 columns the user can select by "disconnected" slicer. You need to read out the slicer selection (e.g. with VALUES) and put it in an IF or SWITCH formula in your measure that then changes to one of your above Act/FC/plan/lastyear formulas. For the variance you just need one formula that divides your two selectable measures.

I finaly made the periods for both selectable measures flexible as well but thats maybe something for week 3