How to Calculate Variance

[serky]

Hi I am new to PowerPivot so please bear with me. I have a pivot in 2007 that I am trying to replicate in Power Pivot 2010. The table is set up as: rows for date, then income or expense and a subtotal; columns for actual, budget and variance. I am having trouble calculating financial variance. My data set is:
cost code, account #, date (month), Type (actual or budget), dollars.
A relationship sets the account as either income or expense.
I have calculated variance using measure 1
=sumx(filter(‘DataAccounts’,’DataAccounts’[Type]=”Budget”),’DataAccounts’[Dollars])-sumx(filter(‘DataAccounts’,’DataAccounts’[Type]=”Actual”),’DataAccounts’[Dollars])
When I display my pivot table, I get 4 columns:
1. date, expense and income as rows
2. actual (dollars and measure 1), budget (dollars and measure 1). In both cases measure 1 = dollars ie – this is not variance. If I include row totals, the total sum of dollars’ and ‘total sum of measure 1′ equal each other and are the correct variance figure.
What am I doing wrong?

 

[scottsen]

Can I get you to re-write your measures, even if I don't know they are "not correct", just to make things... easier to understand?

[Total Dollars - Budget] := CALCULATE(SUM(DataAccounts[Dollars], DataAccounts[Type]="Budget"))
[Total Dollars - Actual] := CALCULATE(SUM(DataAccounts[Dollars], DataAccounts[Type]="Actual"))
[Total Variance] := [Total Dollars - Budget] - [Total Dollars - Actual]

I think this is more readable, more reusable, and better performance (sumx should be avoided when possible).

Then we can talk about if it works or not

 

[serky]

Hi Scott
I made a slight change to your formula (added the red bracket and removed a bracket from the end) = CALCULATE(SUM(DataAccounts[Dollars]), DataAccounts[Type]="Budget") - and it worked perfectly. I also had to remove 'type' as a column on my pivot table else it was showing 6 columns of data ie - 'Total dollar - budget', 'total dollars - actual' and 'total dollars - variance' were repeated twice ( 3 below 'type = actual' and 3 below 'type = 'budget'). I was expecting the table to behave the same way as the standard pivot table and just show actual, budget and variance (where 'variance' was a 'type').

When should sumx be used? I only used sumx as this was an example I found on the web.

Thanks

 

[scottsen]

Awesome, glad that help! (And sorry about the typos)

SUM is always faster, and should always be preferred, but it only accepts a column. Anything more complicated (sum'ing the results of a measure) would require the use of sumx.

 

[lindaphillips]

Calculating variance allows you to measure how far a set of numbers is spread out..