I have the following calculated fields:
Prior Year Gross Sales:
IF
DATEDIFF('year',[Date],TODAY())=1
AND
MONTH([Date])<=MONTH([Max Date Parameter])
THEN
[Gross Sales]
END
Prior Year Gross Units:
IF
DATEDIFF('year',[Date],TODAY())=1
AND
MONTH([Date])<=MONTH([Max Date Parameter])
THEN
[Gross Units]
END
Prior Year Average:
SUM([Prior Year Gross Sales])/SUM([Prior Year Gross Units])
Current Year Gross Sales calculated the same way as Prior Year Gross Sales substituting =1 with =0
Current Year Gross Units calculated the same way as Prior Year Gross Units substituting =1 with =0
Current Year Average:
SUM([Current Year Gross Sales])/SUM([Current Year Gross Units])
I then calculate the Price Variance as:
([Current Average Price]-[Prior Year Average Price])*SUM([Current Gross Units])
and the Volume Variance as:
[Prior Year Average Price]*(SUM([Current Gross Units])-SUM([Prior Year Gross Units]))
How do I build a waterfall chart where I have:
the first bar as PYTD
the second bar as the Volume variance from PTYD
the third bar as the Price variance from the Volume Variance
the fourth column as the YTD?
Thank you for your help
Fabio:
As you are looking to create waterfall charts with multiple measures, check this thread Waterfall chart with multiple measures
Hope this helps.
..kk