Actual v/s Budget calculations using DAX

One of the most common calculations in business is to compare Budget v/s Actual.

In the times of Excel this calculation was a pain in the ass, with tons of scattered vlookups, sumifs, pivots on top of pivots across hidden sheets to finally arrive at that one pivot table that management wanted. One could really sweat over the ordeal of doing the grunt work!

But now that we have Power BI with a lot more power, I am going to share with you how can you do clean and dynamic Budget v/s Actual calculations in Power BI using DAX!

Let’s jump right in..

Consider these 2 tables..

In the sales table we have…

• Five standard columns – Date, Region, Product ID, Unit Sales and Unit Price
• And this is 20 months of sales data from Jan 2011 to Aug 2012

In the Budgets Table we have – 2 Years of Annual Budgets (Unit Sales) by Product ID

Actual v/s Budget Calculation

In order to do the most coveted Actual v/s Budget calculation we need the following

1. Actual Sales – Which is easy (Price x Units)
2. Budget Sales – Although this is just the sum of Budget Units column from the Budgets table but then unless you want to see the budgets only at the year level, we’ll need to allocate the annual budget to individual transactions (from sales table). This would enable you to answer magical questions like..
   • Are my quarterly sales meeting the target ?
   • Are my monthly or even daily sales meeting the target ?

Real quick a measure for Actual Sales

Actual Sales =
SUMX ( Sales, Sales[Units] * Sales[Price] )

Relationship Between Sales and Budgets Table

On the face of it there doesn’t seem to be a relationship between the two tables. But but.. If you think about it we can actually concatenate Year and Product ID from both tables to create a One to Many Relationship

So in the Budgets Table – Create a Concat column

And in the Sales table again a Concat Column

Rest is obvious, create a one to many relationship between Budgets (one side) and Sales (many side) using the new Concat column created

Measure for Budget Sales

There could be two approaches to solve for budget sales

1. By creating a column in the Budgets Table for the number of transactions for the Product and it’s Year
2. Or directly writing a measure without any column (also my preferred choice)

#1 – The Column Method

Adding a new column in the budgets table

Per Transaction Target = 
DIVIDE ( 1, COUNTROWS ( RELATEDTABLE ( sales ) ) ) * Budgets[Budget Units Sales]

Let me explain this

1. First I try to find how many transactions (rows) matching with the current product and year
2. Dividing by 1 and multiplying that with annual unit sales budget (for that product) will return the per budgeted units per transaction. Nothing complicated, It’s 7th grade unitary method math

A few more things

1. Rather than equally dividing the budgeted units between the sales transactions, one could argue an allocation based on parameters such as Last Year Sales or Product Level Contribution to Total Revenue
2. But for now, the idea is to explore allocation. So I am going the easiest way to explain

Now that we have the per transaction budget I can write a simple measure

Budget Sales =
SUMX ( Sales, RELATED ( Budgets[Per Transaction Target] ) * Sales[Price] )

In english the above measure would mean like this..

• For every row of the sales table, do a Vlookup and get’s its transactional level target units
• Multiplying each transactional level target units with price would give the Budget Sales (in value terms)

#2 – The Measure Method – Creating columns is not that a bad idea but why not try creating a measure instead. Which means that I’ll have to count the transactions of each product and it’s year within the measure. Try this..

Budget Sales Measure =
SUMX (
    Sales,
    DIVIDE (
        1,
        COUNTROWS (
            FILTER ( 
                ALL ( Sales ), 
                EARLIER ( Sales[Concat] ) = Sales[Concat] 
            )
        )
    ) * Sales[Price] * RELATED ( Budgets[Budget Units Sales] )
)

Adjusted Budget = 
SUMX(
    Sales,
    VAR __FirstDate = 
    CALCULATE(
        STARTOFYEAR(Cal[Date]),
        Sales[Concat] = EARLIER(Sales[Concat])
    )
    VAR __LastDate = 
    CALCULATE(
        EOMONTH(LASTDATE(Sales[Date]),0),
        ALL(Sales),
        Sales[Concat] = EARLIER(Sales[Concat])
    )
    VAR __DaysinYear = 
    CALCULATE(
        COUNTROWS(Cal),
        ALL(Cal),
        YEAR(Cal[Date])=RELATED(Budgets[Year])
    )
    VAR PctAllocation = 
    DIVIDE(
        VALUE(__LastDate - __FirstDate + 1),
        __DaysinYear
    )
    RETURN
    DIVIDE(
        1,
        COUNTROWS(
            FILTER(
                ALL(Sales),
                Sales[Concat]=EARLIER(Sales[Concat])
            )
        )
    ) * RELATED(Budgets[Budget Units Sales]) * Sales[Price] * PctAllocation    
)

New Variance = [Actual Sales]- [Adjusted Budget]

How differently would you have solved this, have a simpler measure? Share it in the comments

More on DAX

1. Top Selling Product Analysis in Power Pivot
2. DAX Puzzle 1
3. DAX Puzzle 2
4. DAX Puzzle 3
5. How to Correctly use IF in Power BI
6. Change Pivot Table Field Calculations with a Slicer
7. Calculating Fiscal Year in Power BI