Budget vs Actuals Dashboard Best Practices

[zeppelin77bc]

Hi,

I am trying to create a multi year budget vs actual Power BI report. I will have several instances where different GL Cost Center Date combinations differ from budget to actuals.   (Cost Center A for January for Sales is 0 in budget but 100K in actuals).

How do I set up the relationships knowing that they are two different tables and how do I get the comparisons to work in meaningful way? (Matrix that shows actuals vs budgets with nested income satements).

I just don't see too many great examples of how to accomplish this.

Data

Cost Center GL GL Rollup IS Type  Month Amount

Type would be IS or BS and

GL Rollup would be (Sales, Cost of Sales, Salaries and Wages, etc, etc)

Thanks

[Shahid12523]

Budget vs Actuals best practice (short):

Use star schema → Date, GL, Cost Center, IS Type as dimensions.

Put Actuals + Budgets into one fact table with a [Scenario] column (Actual, Budget).

Create measures:

Actuals = CALCULATE(SUM(Fact[Amount]), Fact[Scenario] = "Actual")
Budget = CALCULATE(SUM(Fact[Amount]), Fact[Scenario] = "Budget")
Variance = [Actuals] - [Budget]
Variance % = DIVIDE([Variance],[Budget])

Matrix: Rows = GL hierarchy (IS type → Rollup → Account → Cost Center), Columns = Scenario.

Add visuals (variance waterfall, trend lines) for insight.

👉 Unified fact table with [Scenario] makes comparisons clean and scalable.

[zeppelin77bc]

Hi,

I am interested in your approach because of the gaps in budget vs actual activity. Are you appending these to get them into one table?  

[v-aatheeque · Accepted solution]

Hi @zeppelin77bc 

yes, they’re appended into one fact table so the model stays scalable and easy to maintain.

[v-aatheeque]

Hi @zeppelin77bc 

We haven’t heard from you on the last response and was just checking back to see if your query was answered, Otherwise, will respond back with the more details and we will try to help .

[v-aatheeque]

Hi @zeppelin77bc 

We’d like to check if you were able to go through the previous response to your issue. Please let us know if you need further clarification we’ll do our best to support you.

[v-aatheeque]

Hi @zeppelin77bc 

Have you had a chance to look at the earlier response? Let us know if anything is unclear, and we’ll provide further clarification.

[Royel]

Hi @zeppelin77bc  

You can create a star schema with your Fact tables (Sales and Budget) and some other helpful dimentional tables like (Date, Cost Center, GL Account and so on)

Your possible mode could be look like this. 

Here i consider only 1 Date table, however if you do not have day level data mostly in the Budget table, you need to maintain 2 seperate Date table. 

By using this structure you no need to worry about if you do not have data at the begining. 

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

[FBergamaschi]

Hi @zeppelin77bc 

you have two fact tables if I understand correctly (one with actuals, another with budget).

If that is the case, you can create a start schema like this

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[zeppelin77bc]

Thanks. I really appreciate it.  Appending the tables is really messy with a large data set so setting up tables for budget, actual, cost center, dates and accounts works best.