Compare Budgeted Scenarios vs. Actuals

When working with financial data, it’s very common to want to compare budgeted scenarios vs. actuals. For example, we might want to compare all the budgeted scenarios against the actual scenario or maybe we just want to select one budgeted scenario to compare against the actual scenario. In this post, I’m going to show you a technique that will help you accomplish exactly that.

Let’s first look at our data. It contains data for various scenarios, as well as the data for actual scenarios.

To create our solution, we first need to create another table that will contain unique values for all the budgeted scenarios. In our sample data, we have 4 unique scenarios (excluding actuals).

Our Scenario table will look something like this:

🤚

Make sure the Scenario table doesn’t have a relationship with the Data table. If the relationship is auto-created, please go to the relationship tab to remove the relationship.

Next, we need to create a Scenario slicer from the Scenario table. Then we change the slicer settings, as shown below, so that we can select more than one budgeted scenario to compare against the actual scenario.

• Turn off Multi-select with CTRL
• Turn on Show “Select all” option

We only need one measure to do all the work with us, and this is the measure that we will use in our visuals:

Scenario Amount =
VAR __selectedScenarios =
UNION (
    VALUES ( Scenario[Scenario] ),  --get a list of all selected sceanarios in the slicer
    { "Actuals" }   --add "Actuals" in the selected slicer list
)
RETURN
CALCULATE (
    SUM ( Data[Amount] ),
    KEEPFILTERS (
        TREATAS ( __selectedScenarios, Data[Scenario] )
    )
)

Let’s break down the above DAX expression:

• The VAR __selectedScenarios part of the expression creates a table depending on which values you have selected from the slicer. It also adds a row for the actual scenario under consideration. We need the actual scenario regardless of the selected scenarios because there needs to be something to compare the selected budgeted scenarios against. For example, if we selected Scenario A and Scenario D in the slicer, the above VAR expression wil return a table with 3 rows: 2 for the projected scenarios and 1 for the actual scenario.

• The RETURN part of the DAX expression is using TREATAS to filter our Data table (it is basically used to filter unrelated tables).

To utilize this measure, I created a Stacked Column Chart visual: I added the Scenario column from the Data table on the x-axis and added the Scenario Amount measure under “Values”. I also created a measure to depict the bars for the actuals in a different colour. This is optional but adds a nice touch:

Actual Bar Color = IF ( SELECTEDVALUE ( Data[Scenario] ) = "Actuals", "Red" ) 

I hope this blog post gave you an idea of how you can use this technique to meet your business needs! Leave your comments below to let me know what you think or to share how you plan to use this solution.

Useful Links:

Learn more about TREATAS here.
Learn more about KEEPFILTERS here.
Learn more about Conditional Formatting here.