Forum Discussion

Krishna1826's avatar
Krishna1826
Frequent Visitor
10 months ago
Solved

Waterfall chart with multiple measures

I have 13 measures  which i want to show in waterfall chart but the way i want to display is tricky which is what i am not able to figure it out. Kindly help me   Measures: Measure 1 Measure 2 M...
Need Help
  • v-dineshya's avatar
    v-dineshya
    10 months ago

    Hi Krishna1826 ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    You have 13 measures, and you want a single waterfall chart where some measures start fresh from zero and others continue cumulatively. This is not directly supported by Power BI native waterfall, but it work using a disconnected table.

     

    Please follow below steps.

     

    1. Created disconnected table with sample data.

     

     

    2. Created Below Measure .

     

    Waterfall Value =
    VAR CurrentOrder = SELECTEDVALUE(MeasureOrder[Order])
    VAR CurrentGroup = SELECTEDVALUE(MeasureOrder[Group])
    VAR RunningTotal =
        CALCULATE(
            SUMX(
                FILTER(
                    MeasureOrder,
                    MeasureOrder[Order] <= CurrentOrder &&
                    MeasureOrder[Group] = CurrentGroup
                ),
                MeasureOrder[Value]
            )
        )
    RETURN
    RunningTotal
     
    3. Please refer below Output snaps and attached PBIX file
     

     

     

     

     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh

Nasif_Azam's avatar
Nasif_Azam
Super User
10 months ago

Hey Krishna1826 ,

Try tge following steps:

 

1) Create a Helper Table: Create a table with columns for Step, Measure, Value, and IsStart to manage the flow and resets for each set of measures.

 

2) Create a DAX Measure:  Use a DAX measure to calculate the Cumulative Value. Reset the value to 0 when IsStart = TRUE and accumulate the values for subsequent measures.

Cumulative Total = 
IF(
   VALUES('HelperWaterfall'[IsStart]) = TRUE(), 
   0, 
   CALCULATE(
     SUM('HelperWaterfall'[Value]), 
     FILTER(
       ALL('HelperWaterfall'), 
       'HelperWaterfall'[Step] <= MAX('HelperWaterfall'[Step])
     )
   )
)

 

 

3) Build the Waterfall Chart:

 

 

Please also check the Watefall Chart.pbix file. If there any queries let me know.

 

Best Regards,
Nasif Azam