Compare different scenarios against Actuals 2023

[jlming]

Hi everyone,

I am struggling creating a waterfall chart for a certain use case, and it seems impossible to me. I must be doing something wrong with DAX, so maybe you guys can help me out. I appreciate your help on this.

So I haver a simple data model for energy projects where the fact table contains the following fields:

Project Name

Project Portfolio

Project Country

Project Status as of 2024 Year End (Operation, Under Construction, Ready to Build)

Scenario (Actuals 2023, Budget 2024, Forecast 1+11...)

NameScenario (this is the combination of Name+Scenario)

Capacity (MW) + Storage (this is a decimal number, the total capacity for the project)

I have a dimension table connected to Scenario. The name of the table is Slicer_2, and its only column contains the unique values found in the Fact Table, Scenario column.

So the idea is having a slicer connected to the fact table. It will modify the behavior of the a waterfall chart that will always start with Actuals 2023, and then calculate the additions, then the substractions and finally it will show the total (which is the total for the scenario selected in the slicer). That way, the differences between the two scenarios will become very clear.

One difficulty I'm facing is, sometimes it seems to work fine at project level, but when aggregating by portfolio or country, it just does not work. I think this is related to the use of SUMX, I have tried lots of possibilities but I'm not hitting the problem correctly.

I started creating measures for Actuals 2023 and Selected Scenario:

Scenario1_MW = CALCULATE(
    SUM('Total MW'[Capacity (MW) + Storage]),
    Slicer2[Slicer 2] = "Actuals 2023"
    ) + 0

Scenario2_MW = CALCULATE(
    SUM('Total MW'[Capacity (MW) + Storage]),
    Slicer2[Slicer 2] = selectedvalue(Slicer2[Slicer 2])
    ) + 0

Then I calculate the additions:

Additions = sumx(
    VALUES('Total MW'[NameScenario]),
    CALCULATE(
        IF([Scenario2_MW] - [Scenario1_MW] > 0, [Scenario2_MW] - [Scenario1_MW], 0)
    )
)

Here comes the first problem: it calculates fine the additions when the values are positive, but it leaves blanks for the ones that should be 0, and I don't understand why.

Then I calculate the substractions:

Substractions= sumx(
    VALUES('Total MW'[NameScenario]),
    CALCULATE(
        IF([Scenario2_MW] - [Scenario1_MW] < 0, [Scenario2_MW] - [Scenario1_MW], 0)
    )
)

The behavior is the opposite as before. It returns blanks when it should return negative values. And it's returning zeros correctly.

I put together a matrix where I put the following values:

Rows: Status, Portfolio, Project Name

Values:

• Scenario2_MW -> Works fine
• Scenario1_MW -> Works fine
• Additions:
  • Working fine for rows where values are expected to be positive
  • Working wrong for rows where values are expected to be zero
  • Working wrong for subtotals. I'm seeing the same totals as Scenario2_MW
• Substractions:
  • Working wrong for rows where values are expected to be negative
  • Working fine for rows where values are expected to be zero
  • Working wrong for subtotals. I'm seeing 0 as the Grand Total

Any idea? My guess is that there are two problems... one with the filter in the slicer, the other one with the SUMX use.

Thanks!

[jlming]

Thanks for answering. I did put a 0 for the unmet conditions, and that is the result I expect. No blanks should be shown in the table.

Regarding the conditions, your logic is equivalent to mine. I put your code and the result is the same.

My guess is that something is going wrong during the SUMX calculations. Two measures are being used, one of them using a filter made by a dimension table (which is correctly connected to the fact table). It seems something wrong is happening at row level.

I want to add something. If I go to Scenario 2 measure and susbtitute the "selected value" part by a string, like Budget 2024, everything works fine.

Thanks!

[123abc]

Let's address your concerns and refine your measures to achieve the desired behavior.

1. Blanks in Additions and Subtractions:

   • For the blanks in your additions and subtractions, it's likely due to the IF conditions in your calculations. When the condition isn't met, the result is not explicitly defined, leading to blanks. To fix this, you can use BLANK() for clarity.

2. Incorrect Subtraction Results:

   • The issue with your subtraction calculation is likely due to the condition checking for negative values. It seems reversed. You want to subtract Scenario1_MW from Scenario2_MW and return the result if it's negative.

Let's refine your measures:

Scenario1_MW =
CALCULATE(
SUM('Total MW'[Capacity (MW) + Storage]),
Slicer2[Slicer 2] = "Actuals 2023"
)

Scenario2_MW =
CALCULATE(
SUM('Total MW'[Capacity (MW) + Storage]),
Slicer2[Slicer 2] = SELECTEDVALUE(Slicer2[Slicer 2])
)

Additions =
SUMX(
VALUES('Total MW'[NameScenario]),
IF([Scenario2_MW] > [Scenario1_MW], [Scenario2_MW] - [Scenario1_MW], BLANK())
)

Subtractions =
SUMX(
VALUES('Total MW'[NameScenario]),
IF([Scenario2_MW] < [Scenario1_MW], [Scenario2_MW] - [Scenario1_MW], BLANK())
)

By making these adjustments, your measures should correctly calculate the additions and subtractions and handle scenarios where values are expected to be zero. Ensure that the relationships between your tables are properly defined and that the data model is structured correctly to reflect the desired behavior in your waterfall chart.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.