Forum Discussion

arimoldi's avatar
arimoldi
Resolver II
1 year ago
Solved

Waterfall Chart based on Status Variations and Dates

Hi,

 

I need to create a waterfall chart in Power BI based on a dataset like this:

 

REFERENCE_DATECODESTATUSSEVERITY
30/11/2024AONGOINGLOW
30/11/2024BONGOINGLOW
30/11/2024CONGOINGHIGH
31/12/2024AONGOINGLOW
31/12/2024BCLOSEDLOW
31/12/2024CONGOINGHIGH
31/01/2025AONGOINGLOW
31/01/2025CONGOINGMEDIUM
31/01/2025DONGOINGHIGH

 

and the expected final chart should be something like this:

 

So basically all the variations total count of CODEs, any variations of STATUS (every status to CLOSED) and any variations of SEVERITY (any decrease from from HIGH to LOW or MEDIUM) should be treated as -1, while the opposite as +1.

 

The waterfall chart should work according to a slicer based on REFERENCE_DATE, so if I select just 30/11/2024 and 31/01/2025 the chart should consider the variations between these 2 reference date.

 

Any suggestion on how to achieve the final result?

 

Thanks,

Andrea

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi arimoldi 

    Based on your description,   you can refer to the following solution.

    Sample data is the same as you provided.

    1.Create a type table.

     

    2.Create the following measures.

    Level =
IF (
    SELECTEDVALUE ( 'Table'[STATUS] ) = "CLOSED",
    0,
    SWITCH ( SELECTEDVALUE ( 'Table'[SEVERITY] ), "HIGH", 3, "MEDIUM", 2, "LOW", 1 )
)

    Define =
VAR a =
    MAXX (
        TOPN (
            1,
            FILTER (
                ALLSELECTED ( 'Table' ),
                [REFERENCE_DATE] < MAX ( 'Table'[REFERENCE_DATE] )
                    && [CODE] IN VALUES ( 'Table'[CODE] )
            ),
            [REFERENCE_DATE], DESC
        ),
        [Level]
    )
VAR b = [Level]
RETURN
    IF ( b = a || a = BLANK (), "Keep", IF ( b > a, "Upper", "Lower" ) )

    Counts =
VAR a =
    ADDCOLUMNS ( ALLSELECTED ( 'Table' ), "Test", [Define] )
VAR b =
    COUNTROWS (
        FILTER (
            a,
            [Test]
                IN VALUES ( 'Type'[Type] )
                    && [REFERENCE_DATE] IN VALUES ( 'Table'[REFERENCE_DATE] )
        )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Type'[Type] ) = "Lower", 0 - b, b )

    Then create a stacked Column visual, and put the following field to it.

    Output

    Best Regards!

    Yolo Zhu

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

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi arimoldi 

    Based on your description,   you can refer to the following solution.

    Sample data is the same as you provided.

    1.Create a type table.

     

    2.Create the following measures.

    Level =
IF (
    SELECTEDVALUE ( 'Table'[STATUS] ) = "CLOSED",
    0,
    SWITCH ( SELECTEDVALUE ( 'Table'[SEVERITY] ), "HIGH", 3, "MEDIUM", 2, "LOW", 1 )
)

    Define =
VAR a =
    MAXX (
        TOPN (
            1,
            FILTER (
                ALLSELECTED ( 'Table' ),
                [REFERENCE_DATE] < MAX ( 'Table'[REFERENCE_DATE] )
                    && [CODE] IN VALUES ( 'Table'[CODE] )
            ),
            [REFERENCE_DATE], DESC
        ),
        [Level]
    )
VAR b = [Level]
RETURN
    IF ( b = a || a = BLANK (), "Keep", IF ( b > a, "Upper", "Lower" ) )

    Counts =
VAR a =
    ADDCOLUMNS ( ALLSELECTED ( 'Table' ), "Test", [Define] )
VAR b =
    COUNTROWS (
        FILTER (
            a,
            [Test]
                IN VALUES ( 'Type'[Type] )
                    && [REFERENCE_DATE] IN VALUES ( 'Table'[REFERENCE_DATE] )
        )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Type'[Type] ) = "Lower", 0 - b, b )

    Then create a stacked Column visual, and put the following field to it.

    Output

    Best Regards!

    Yolo Zhu

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