Forum Discussion
Post PatronNet Sales Variance in waterfall visual
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!
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.
6 Replies
ABD128Resolver II
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"} })
hidenseek9Thank 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
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?