Forum Discussion
Dynamic Forecast Comparison
Hi,
The table below shows a summary of the data I'm working with. Each row represents a different production forecast for a given year. If I select a Year in the slicer, I want to display a waterfall chart to show the difference between production forecasts for that selected year starting with the first forecast and ending with the last (or Actuals for the current or past years).
The standard waterfall chart works but it gives you subtotals in between each value that I don't want and I can't figure out how to make them go away. I'm assuming to make it work I'll need a measure to find the incremental differences between forecasts. If I'm wrong feel free to point me in the right direction. I have a table where the forecasts are index and the volumes table isn't complicated (see below) but I'm struggling with the DAX.
| Product | Production Date | Volume | Forecast |
| Product1 | 1/1/2018 | 200 | 2018 PB |
| Product2 | 10/1/2018 | 300 | 2018 RBU1 |
| Product3 | 1/10/2019 | 5000 | 2019 MTP |
| Product4 | 4/1/2021 | 675 | Actual |
| Product5 | 6/1/2022 | 238 | 2020 LRP |
GBilger what you can do is calculate a dynamic measure that returns the delta between the forecasts, so as to pass them directly into the chart and thus remove the subtotals, like this:
The graph above is the original one, in the one below I passed the DELTA measure as a value, calculated as follows:
DELTA = CALCULATE(SUM(Tabella[Volume]), ALLSELECTED(Tabella[Production Date ])) - CALCULATE(SUM(Tabella[PREV_VOLUME]), ALLSELECTED(Tabella[PREV_DATE]))where PREV_VOLUME and PREV_DATE are two calculated columns:PREV_DATE = LOOKUPVALUE(Tabella[Production Date ],Tabella[Indice],Tabella[Indice]-1)PREV_DATE = LOOKUPVALUE(Tabella[Production Date ],Tabella[Indice],Tabella[Indice]-1)instead Index is a column that you can insert from the power query in the sorting you prefer to give to the data to calculate the differential.I hope I was clear, let me know,B.
4 Replies
Hi,
The proposed solution did get rid of the labels for the increases/decreases. However I was curious if there is a way to get rid of subtotal bars in between. For this example it would just show the 2019 LRP and Actuals bars. In between would be all the differences.
GBilger what you can do is calculate a dynamic measure that returns the delta between the forecasts, so as to pass them directly into the chart and thus remove the subtotals, like this:
The graph above is the original one, in the one below I passed the DELTA measure as a value, calculated as follows:
DELTA = CALCULATE(SUM(Tabella[Volume]), ALLSELECTED(Tabella[Production Date ])) - CALCULATE(SUM(Tabella[PREV_VOLUME]), ALLSELECTED(Tabella[PREV_DATE]))where PREV_VOLUME and PREV_DATE are two calculated columns:PREV_DATE = LOOKUPVALUE(Tabella[Production Date ],Tabella[Indice],Tabella[Indice]-1)PREV_DATE = LOOKUPVALUE(Tabella[Production Date ],Tabella[Indice],Tabella[Indice]-1)instead Index is a column that you can insert from the power query in the sorting you prefer to give to the data to calculate the differential.I hope I was clear, let me know,B.
