Forum Discussion

aredmond127's avatar
aredmond127
Frequent Visitor
4 years ago

Waterfall Chart Help

Hi - totally new to Power BI and need help on waterfall chart please.

 

I need to show Baseline to Total Saleable, with event type as the breakdown (Baseline + Event = Total Saleable). My data is in the below format, whereby baseline, event & total saleable are determined in the same column (metric). Event Type is in it's own column. I dont have much flexibility with this format, as it is a BEX pull with data limitations. 

 

So far, I have been able to get it into the format stated above, however I cannot get any values to populate accurately. I think I'm missing the measures to calculate the values but I'm not following how I do this (think I need to use SWITCH?).

 

End state I need to have multiple slicer options for users to select their own region, division etc. while maintaining the above visual. There will be between 5 - 10 slicer options by completion. 

 

Any help would be greatly appreciated as I'm pretty lost on this. 

 

WATERFALL VISUAL
-
CATEGORY		FILTER / SLICERFILTER / SLICERFILTER / SLICERWATERFALL VISUAL
-
BREAKDOWN		WATERFALL VISUAL
-
VALUE
MetricRegionDivisionMajor CategoryEvent TypeValue
BaselineUKELMU#319
BaselineAPACELFR#342
BaselineNAMCMU#17
BaselineNATFFR#156
BaselineUKELSK#487
BaselineUKMCMU#460
BaselineAPACMCMU#227
EventUKELMUEvent Type 1384
EventAPACELFREvent Type 2437
EventNAMCMUEvent Type 3232
EventNATFFREvent Type 4265
EventUKELSKEvent Type 5190
EventUKMCMU#358
EventAPACMCMU#86
Total SaleableUKELMU#703
Total SaleableAPACELFR#779
Total SaleableNAMCMU#249
Total SaleableNATFFR#421
Total SaleableUKELSK#677
Total SaleableUKMCMU#818
Total SaleableAPACMCMU#313
      
Need Help

2 Replies

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    Hi aredmond127 ,

     

    Depending on your needs, a separate column needs to be created to count the values of the baseline + events. Then put it in the waterfull chart visual values. The reference is as follows:

    Col_total =
CALCULATE (
    SUM ( 'Table'[Value] ),
    ALLEXCEPT (
        'Table',
        'Table'[Region],
        'Table'[Division],
        'Table'[Major Category]
    )
)


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


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

    • aredmond127's avatar
      aredmond127
      Frequent Visitor
      4 years ago

      Hi Henry, 

       

      Thanks for  the above help - however it's not quite right. Below is the result I'm getting, where baseline = total saleable, with no events values in between. 

       

      To clarify, the graph should show baseline on the left, with events as the breakdown and total saleable on the right hand side. For example,

       

      Baseline = 10 

      Event 1 = 2

      Event 2 = 5 

      Event 3 = -3

      Total Saleable = 14

       

       

       

      To provide clarification, please see total example below. I've trimmed down the data set but there would be >1M rows of data, multiple divisions, regions, major categories etc. plus multiple other columns not included, that could act as further filters. 

       

      From the data, I need to show the waterfall in the below format. The number of filters applied and combination of filters is user determined and could be any number of options (further examples below).

       

      Hope this makes sense - thanks again for the help.