Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
bgonen6899
Regular Visitor

Variances between Scenarios

I have several scenarios and when I choose the two scenarios the selected values are showing on the table correctly (see table below).

The variances should be most recent scenario Minus the Older scenario for example Budget 2024 minus Actual 2023

bgonen6899_0-1731639916864.png

 

I am using the following DAX but I keep getting blanks:

 

Scenario Variance =
VAR SelectedScenario1 = SELECTEDVALUE(Scenarios[Scenario], BLANK())
VAR SelectedScenario2 = SELECTEDVALUE(Scenarios[Scenario], BLANK())

RETURN
    IF(
        AND(NOT ISBLANK(SelectedScenario1), NOT ISBLANK(SelectedScenario2)),
        SWITCH(
            TRUE(),
            SelectedScenario1 = "Forecast 10" && SelectedScenario2 = "Budget 2024", [Forecast 10] - [Budget 2024],
            SelectedScenario1 = "Plan 2025" && SelectedScenario2 = "Forecast 10", [Plan 2025] - [Forecast 10],
            SelectedScenario1 = "Plan 2025" && SelectedScenario2 = "Budget 2024", [Plan 2025] - [Budget 2024],
            SelectedScenario1 = "Plan 2025" && SelectedScenario2 = "Actual 2023", [Plan 2025] - [Actual 2023],
            BLANK()
        ),
        BLANK()
    )

 

 

1 ACCEPTED SOLUTION
Kedar_Pande
Resident Rockstar
Resident Rockstar

@bgonen6899 

Here’s an updated DAX measure to handle this:

Scenario Variance =
VAR SelectedScenarios = VALUES(Scenarios[Scenario])
VAR Scenario1 = MAXX(SelectedScenarios, Scenarios[Scenario])
VAR Scenario2 = MINX(SelectedScenarios, Scenarios[Scenario])
VAR Scenario1Value =
SWITCH(
TRUE(),
Scenario1 = "Forecast 10", [Forecast 10],
Scenario1 = "Plan 2025", [Plan 2025],
Scenario1 = "Budget 2024", [Budget 2024],
Scenario1 = "Actual 2023", [Actual 2023],
BLANK()
)
VAR Scenario2Value =
SWITCH(
TRUE(),
Scenario2 = "Forecast 10", [Forecast 10],
Scenario2 = "Plan 2025", [Plan 2025],
Scenario2 = "Budget 2024", [Budget 2024],
Scenario2 = "Actual 2023", [Actual 2023],
BLANK()
)
RETURN
IF(
AND(NOT ISBLANK(Scenario1Value), NOT ISBLANK(Scenario2Value)),
Scenario1Value - Scenario2Value,
BLANK()
)

Give this a try, and let me know how it works!

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

View solution in original post

7 REPLIES 7
Bibiano_Geraldo
Memorable Member
Memorable Member

Hi @bgonen6899 ,

Please try the bellow DAX:

Scenario Variance =
VAR SelectedScenario1 = SELECTEDVALUE(Scenarios[Scenario], BLANK())
VAR SelectedScenario2 = SELECTEDVALUE(Scenarios[Scenario], BLANK())

RETURN
    IF(
        AND(NOT ISBLANK(SelectedScenario1), NOT ISBLANK(SelectedScenario2)),
        SWITCH(
            TRUE(),
            SelectedScenario1 = "Forecast 10" && SelectedScenario2 = "Budget 2024", [Forecast 10] - [Budget 2024],
            SelectedScenario1 = "Plan 2025" && SelectedScenario2 = "Forecast 10", [Plan 2025] - [Forecast 10],
            SelectedScenario1 = "Plan 2025" && SelectedScenario2 = "Budget 2024", [Plan 2025] - [Budget 2024],
            SelectedScenario1 = "Plan 2025" && SelectedScenario2 = "Actual 2023", [Plan 2025] - [Actual 2023],
            BLANK()
        ),
        BLANK()
    )

 

Thank you

I hope this helps! 
If you found this answer helpful:
✔️ Mark it as the solution to help others find it faster.
 Give it a like to show your appreciation!

Thank you for contributing to our amazing Power BI community! 

Thank you Bibianco,

 

I like the logic but for some reason all the combinations return blanks

Kedar_Pande
Resident Rockstar
Resident Rockstar

@bgonen6899 

Here’s an updated DAX measure to handle this:

Scenario Variance =
VAR SelectedScenarios = VALUES(Scenarios[Scenario])
VAR Scenario1 = MAXX(SelectedScenarios, Scenarios[Scenario])
VAR Scenario2 = MINX(SelectedScenarios, Scenarios[Scenario])
VAR Scenario1Value =
SWITCH(
TRUE(),
Scenario1 = "Forecast 10", [Forecast 10],
Scenario1 = "Plan 2025", [Plan 2025],
Scenario1 = "Budget 2024", [Budget 2024],
Scenario1 = "Actual 2023", [Actual 2023],
BLANK()
)
VAR Scenario2Value =
SWITCH(
TRUE(),
Scenario2 = "Forecast 10", [Forecast 10],
Scenario2 = "Plan 2025", [Plan 2025],
Scenario2 = "Budget 2024", [Budget 2024],
Scenario2 = "Actual 2023", [Actual 2023],
BLANK()
)
RETURN
IF(
AND(NOT ISBLANK(Scenario1Value), NOT ISBLANK(Scenario2Value)),
Scenario1Value - Scenario2Value,
BLANK()
)

Give this a try, and let me know how it works!

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

By the way, I ran all the possible scenarios are reflecting correct variances.

 

However, Forecast 10 vs. Budget 2024 reflects opposite variances.

 

I wonder if there is a way to rank the scenarios meaning:

1.Actual 2023  (because it is the oldest scenario)

2.Budget 2024 (it is the second oldest scenario)

3."2024 Forecast 10" (normally is completed 3rd quarter of year 2024)

4."Plan 2025" most recent scenario

Hello Kedar

This works perfectly.

Thank you so much.

DataNinja777
Super User
Super User

Hi @bgonen6899 ,

 

My observation regarding your variance analysis is that it is not possible to simultaneously select "Forecast 10" and "Budget 2024." As a result, your output is displaying as blank. I recommend creating specific scenarios for each variance comparison, such as "Forecast 10 vs Budget 2024." Alternatively, for a more dynamic approach, you can make the variance scenarios respond to two slicer selections.

A common error in comparisons across multiple future plans arises when the year dimension is included in the analysis. Filtering the year dimension to 2024 will inadvertently exclude data from other years, such as 2025, 2026, and 2027.

 

Best regards,

amitchandak
Super User
Super User

@bgonen6899 , If they from same table you should use min and max

 

Scenario Variance =
VAR SelectedScenario1 = Minx(allselected(Scenarios), Scenarios[Scenario]))
VAR SelectedScenario2 = Maxx(allselected(Scenarios), Scenarios[Scenario]))

 

 

or use two slicer one with disconnected table 

Compare Categorical Data Using Slicers - Compare two Brands: https://youtu.be/exN4nTewgbc

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.