Net Sales Variance in waterfall visual

[hidenseek9]

Hi, 

I would like to create a dynamic waterfall chart on Net Sales Variance, but I am struggling to find a solution. 

I would like to show NS variance with 3 waterfall blocks (volume variance, mix variance and price variance)

Vol variance = (vol CY- vol LY) x LY total NS/vol

Mix variance = (vol CY- vol LY) x (LY brand NS/vol - LY total NS/vol)

Price variance = (NS CY-NS LY) - vol variance - mix variance 

I think I can create a measure for each, but in order to show it in a waterfall visual, these measures need to be set in a table format. 

However, since this is dynamic with Switch True measure, I am not sure how to make this work. 

I am unable to share my data, unfortunately due to the restriction in cloud

Below is what I have created so far. 

I am using Switch True measures to select this year's sales measure (actual or plan) and the base value (last year or forecast plan)

The waterfall is shown by brand. 

Appreciate the support!

[hidenseek9]

@ABD128 

ISINSCOPE only works for volume variance actually because for both mix and price variance, calculating at total level is not correct. 

Effectively, each mix and price variance calculated at each brand level need to be summed up at total. 

how can this be possible?

[ABD128]

Hi @hidenseek9 

Please try the below:

Use SWITCH(TRUE()) logic to dynamically switch between Actual, Plan, etc. Create measures for NS and Volume (CY and LY) Create 3 Variance measures: [Vol Var] [Mix Var] [Price Var] Create a disconnected table for Waterfall steps: Final dynamic measure: DAXCopyEditWaterfall Value = SWITCH(     SELECTEDVALUE('Waterfall Steps'[Step]),     "Volume Variance", [Vol Var],     "Mix Variance", [Mix Var],     "Price Variance", [Price Var] ) DAXCopyEditWaterfall Steps = DATATABLE("Step", STRING, {   {"Volume Variance"},   {"Mix Variance"},   {"Price Variance"} })  

[hidenseek9]

@ABD128 

Thank you for the suggestion. 

Perhaps, I was not clear. 

On NS variance, the comparison is not always CY to LY. 

This can change. 

Specifically, it can be any one of below and more. 

• CY vs LY
• AOP (Forecast Plan) vs LY
• RF (Forecast Plan) vs LY
• RF (Forecast Plan) vs AOP (Forecast Plan), etc. 

However, I cannot create measures for every single scenario since it's too much. 

How can I solve this issue?

[ABD128]

Hi @hidenseek9 

Use two disconnected slicers to select the Base and Comparison phases (e.g., LY, AOP, Actual), then create dynamic measures using SWITCH() to return the correct Net Sales and Volume values based on those selections. Use these dynamic values in your Volume, Mix, and Price variance formulas to power a single, flexible waterfall chart that adjusts to any comparison scenario.

Switch Function

Selected NS = SWITCH(TRUE(), SelectedPhase = "Actual", [NS ACT], SelectedPhase = "AOP", [NS AOP], .

[hidenseek9]

@ABD128 

Your solution almost worked except for one issue. 

All 3 measures (volume variance, mix variance, and price variance) are calculating correctly as per below screenshot. 

However, they are not added correctly under total on the bottom row. 

Actual sum for each variance is,

Vol: 2.61

Mix: -0.65

Price: +1.96

However, in the visual, mix and price show different value and the total does not add up to 3.92, but 4.1. 

Net Sales Variance is 3.92 underlined in red. 

Because of this, the waterfall is showing a wrong value.

Do you know how to solve this issue?

[ABD128 · Accepted solution]

Hi @hidenseek9

 You need to modify each variance measure to handle totals separately using ISINSCOPE() or HASONEVALUE().

Volume Variance

Volume Var Fixed =

VAR TotalVol_Base = CALCULATE([Selected Vol Base], REMOVEFILTERS('Brand')) VAR TotalNS_Base = CALCULATE([Selected NS Base], REMOVEFILTERS('Brand')) VAR Vol_Base = [Selected Vol Base] VAR Vol_Comp = [Selected Vol] RETURN IF( ISINSCOPE('Brand'[Brand]), -- Row level (Vol_Comp - Vol_Base) * DIVIDE(TotalNS_Base, TotalVol_Base), -- Total level (CALCULATE([Selected Vol], REMOVEFILTERS('Brand')) - CALCULATE([Selected Vol Base], REMOVEFILTERS('Brand'))) * DIVIDE(TotalNS_Base, TotalVol_Base) )

Apply Similar Fix to Mix and Price Variance

Mix Var Fixed:

Mix Var Fixed = VAR TotalVol_Base = CALCULATE([Selected Vol Base], REMOVEFILTERS('Brand')) VAR TotalNS_Base = CALCULATE([Selected NS Base], REMOVEFILTERS('Brand')) VAR BrandVol_Base = [Selected Vol Base] VAR BrandNS_Base = [Selected NS Base] VAR Brand_Mix = (BrandNS_Base / BrandVol_Base) - (TotalNS_Base / TotalVol_Base) RETURN IF( ISINSCOPE('Brand'[Brand]), ([Selected Vol] - [Selected Vol Base]) * Brand_Mix, -- Recalculate at total level (CALCULATE([Selected Vol], REMOVEFILTERS('Brand')) - CALCULATE([Selected Vol Base], REMOVEFILTERS('Brand'))) * ( DIVIDE(CALCULATE([Selected NS Base], REMOVEFILTERS('Brand')), CALCULATE([Selected Vol Base], REMOVEFILTERS('Brand'))) - DIVIDE(TotalNS_Base, TotalVol_Base) ) )

Price Var Fixed:

Price Var Fixed =

VAR NS_Comp = [Selected NS] VAR NS_Base = [Selected NS Base] VAR VolVar = [Volume Var Fixed] VAR MixVar = [Mix Var Fixed] RETURN (NS_Comp - NS_Base) - VolVar - MixVar

Then use the above DAX in Waterfall Chart measure.