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.