Separate Your Best & Worst Performers vs Budgets - Power BI & DAX


#1

#2

Hey Sam,

This is pretty useful and a great way to visualise over/under performance. I’m trying to replicate this in one of our commercial reports, but my data model is set up quite differently from yours.

I’ve got a couple of Fact Tables - one for the ‘Sales Data’, and another for Budgets. I have a budget value to a week level for each customer.

image

I just can’t manage to visualise the right way to do this calculated column in my customer table with a differing model structure.


#3

It doesn’t look to different. A lot of similarities.

I’m probably going to have to see what formula you are using and a bit more detail on the table headers.

Why don’t you think it working? Where are you currently getting up to.

There will be a way to solve this with formula I’m sure.


#4

I think the biggest difference is that in your model you have the City (customer in my case) and the year in one table, but my customer table doesn’t have a year.

So what I feel like I need to do is create a virtual table containing each customer, financial year, YTD budget , YTD actual and calculate the Over/Under value against that.


#5

So I created an additional table using SUMMARIZE

Table = SUMMARIZE(Budgets,Budgets[Customer Code],Budgets[Financial Year])

And then did a calculated column in pretty much the same way that you did.

Performance =
IF (
    CALCULATE (
        [Variance TEU YTD],
        FILTER ( 'Date', 'Date'[Financial Year] = 'Table'[Financial Year] )
    )
        < 0,
    "Under Budget",
    "Over Budget"
)

The formula works on its own, but now I have an additional table to work with. I think there is a smarter way that I just haven’t realised yet.


#6

Potentially this isn’t a bad solution.

Maybe this actually integrates better into your model this way and you can just hide the other existing budgeting table.

Dealing with weeks is always harder in Power BI, due to not being able to use many time intelligence functions.

Month or years simplify things a lot in my opinion


#7

Yeah I agree - weeks are challenging because of the many different interpretations.

It seems I haven’t got it quite right, it is returning some incorrect results. Will keep working on it!