Budget vs Actual Help

Question

Hello, I have two tables which already have a relationship in BI.

1st table - 'Budgeted Table' Contains Budgeted Hours

2nd table - 'Actual Hours Table' Contains Actual Hours

I have 3 categories: Design, Operating Effectiveness, Rollforward for both tables that are important to see our budget vs. actual.

On the second table, I also just have one column with the Total Actual Hours of all 3 categories which looks great for a breakdown of our tasks by the month in a matrix (seen in table below). What I want to do is take things a step further, and essentially create a condition to say for each category that if Sum of Actual Category Hours is greater than Budgeted Category Hours, then highlight in a certain color. For example, in the total Actual Hours for all 3 categories (used in table below), I want to say if the sum of Design Actual Hours is greater than Design Budgeted Hours, then highlight so the specific month in which it went over is highlighted in a different color for each of the three categories that went over budget. As a reminder, I'm currently using the Actual Hours column of all 3 categories to make everything in the table below. How do I achieve what I'm looking for without making my matrix table look too busy? I don't want to see the Design, Operating Effectiveness and Rollforward individual columns in my matrix, I just want to build formulas to do that on the back end using the Total Actual Hours of all 3 categories field.

cengizhanarslan · Accepted Answer

Step 1) Create category Actual + Budget measures
Actual Hours =
SUM ( 'Actual Hours Table'[Hours] )

Budget Hours =
SUM ( 'Budgeted Table'[BudgetHours] )

Actual Design =
CALCULATE ( [Actual Hours], 'Category'[Category] = "Design" )

Budget Design :=
CALCULATE ( [Budget Hours], 'Category'[Category] = "Design" )

Actual OE =
CALCULATE ( [Actual Hours], 'Category'[Category] = "Operating Effectiveness" )

Budget OE =
CALCULATE ( [Budget Hours], 'Category'[Category] = "Operating Effectiveness" )

Actual Rollforward =
CALCULATE ( [Actual Hours], 'Category'[Category] = "Rollforward" )

Budget Rollforward =
CALCULATE ( [Budget Hours], 'Category'[Category] = "Rollforward" )
&nbsp;
Step 2) Your visible matrix value stays simple
Actual Total =
[Actual Design] + [Actual OE] + [Actual Rollforward]
&nbsp;
Step 3) Build a “color” measure for conditional formatting
OverBudget Color =
VAR OverDesign = [Actual Design] &gt; [Budget Design]
VAR OverOE     = [Actual OE] &gt; [Budget OE]
VAR OverRoll   = [Actual Rollforward] &gt; [Budget Rollforward]
RETURN
SWITCH (
    TRUE(),
    OverDesign, "#FFCDD2",        -- light red
    OverOE,     "#FFF9C4",        -- light yellow
    OverRoll,   "#C8E6C9",        -- light green
    BLANK()
)
&nbsp;
4) Apply it to your matrix
Matrix → Format → Cell elements (or Conditional formatting) → Background color → Format by: Field value → pick [OverBudget Color].

Forum Discussion

Budget vs Actual Help

Step 1) Create category Actual + Budget measures

Step 2) Your visible matrix value stays simple

Step 3) Build a “color” measure for conditional formatting

4) Apply it to your matrix

1 Reply

Step 1) Create category Actual + Budget measures

Step 2) Your visible matrix value stays simple

Step 3) Build a “color” measure for conditional formatting

4) Apply it to your matrix

Related content

Working with Budgets

Budget, actual and variance matrix

Monthly sales budget data allocation to daily grain

Budget vs Actual

Budget vs Actuals Dashboard Best Practices