Forum Discussion
Net Sales Variance in waterfall visual
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.
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?
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.