Forum Discussion

bgonen6899's avatar
bgonen6899
Regular Visitor
1 year ago
Solved

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 sc...
Need Help
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    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

Kedar_Pande's avatar
Kedar_Pande
Icon for Super User rankSuper User
1 year ago

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

bgonen6899's avatar
bgonen6899
Regular Visitor
1 year ago

Hello Kedar

This works perfectly.

Thank you so much.