# How to Calculate Variance



## serky (Jun 30, 2014)

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 (Jul 1, 2014)

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 (Jul 2, 2014)

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 (Jul 2, 2014)

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 (Jul 3, 2014)

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


----------

