Waterfall Chart Help

[aredmond127]

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 / SLICER	FILTER / SLICER	FILTER / SLICER	WATERFALL VISUAL - BREAKDOWN	WATERFALL VISUAL - VALUE
Metric	Region	Division	Major Category	Event Type	Value
Baseline	UK	EL	MU	#	319
Baseline	APAC	EL	FR	#	342
Baseline	NA	MC	MU	#	17
Baseline	NA	TF	FR	#	156
Baseline	UK	EL	SK	#	487
Baseline	UK	MC	MU	#	460
Baseline	APAC	MC	MU	#	227
Event	UK	EL	MU	Event Type 1	384
Event	APAC	EL	FR	Event Type 2	437
Event	NA	MC	MU	Event Type 3	232
Event	NA	TF	FR	Event Type 4	265
Event	UK	EL	SK	Event Type 5	190
Event	UK	MC	MU	#	358
Event	APAC	MC	MU	#	86
Total Saleable	UK	EL	MU	#	703
Total Saleable	APAC	EL	FR	#	779
Total Saleable	NA	MC	MU	#	249
Total Saleable	NA	TF	FR	#	421
Total Saleable	UK	EL	SK	#	677
Total Saleable	UK	MC	MU	#	818
Total Saleable	APAC	MC	MU	#	313

[v-henryk-mstf]

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]

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.