Forum Discussion
Helper IWaterfall visual from several measures
Hi all!
I have several measures;
Forecast Spend, Price, Volume, Efficiency and Actual.
From these measures, I want to make a waterfall visual. Like this.
As I understand this not possible. But the solution could be to create a new measure whare these 5 measures could be one set of data that is needed for waterfall visual.
Thank you in advance!
Hi baribir ,
You could UNION your measures into a calculated table.
NewTable =
UNION(
SUMMARIZE ( 'T1', "category","1.Forecast","Value", [m_Forecast]),
SUMMARIZE ( 'T1', "category","2.Price","Value", [m_Price]),
SUMMARIZE ( 'T1', "category","3.Volume","Value", [m_Volume]),
SUMMARIZE ( 'T1', "category","4.Actual", "Value",[m_actuals])
)The above would calculate just overall totals, so it could be that you wantmore columns in your table, for instance period.
Now you can create your waterfall chart, only nowadays you can't remove the TotalColumn from the chart anymore.
Hope this answers you question.
Jan
Hi baribir ,
A calculated table is never effected by report filters during its creation.
The fact that the visuals are not effected by the filters (if you did not any fields to the calculated table) is because the calculation only shows the totals of your measures. You need to add columns for for period and ID to the calculation table in order to be able to use filter on the chart.
So when I add the Period to the table calculation
And link the periods to the calculated table I can again use a filter on the visual
I don't know the complexity of your measures, but it is also worth to consider creating the table during data load.
No idea how you can inverse the data label for the actuals (without changing the value of course).
Jan
4 Replies
Hi baribir ,
You could UNION your measures into a calculated table.
NewTable =
UNION(
SUMMARIZE ( 'T1', "category","1.Forecast","Value", [m_Forecast]),
SUMMARIZE ( 'T1', "category","2.Price","Value", [m_Price]),
SUMMARIZE ( 'T1', "category","3.Volume","Value", [m_Volume]),
SUMMARIZE ( 'T1', "category","4.Actual", "Value",[m_actuals])
)The above would calculate just overall totals, so it could be that you wantmore columns in your table, for instance period.
Now you can create your waterfall chart, only nowadays you can't remove the TotalColumn from the chart anymore.
Hope this answers you question.
Jan
baribirHi Anonymous ,
Thank you that worked. But there is one issue filters and slicers do not affect this visual. But when I press the filter icon on visual shows the affecting filters and slicers.
And to make the visual more similar to what is needed I have inversed the sign of Actuals.
Hi baribir ,
A calculated table is never effected by report filters during its creation.
The fact that the visuals are not effected by the filters (if you did not any fields to the calculated table) is because the calculation only shows the totals of your measures. You need to add columns for for period and ID to the calculation table in order to be able to use filter on the chart.
So when I add the Period to the table calculation
And link the periods to the calculated table I can again use a filter on the visual
I don't know the complexity of your measures, but it is also worth to consider creating the table during data load.
No idea how you can inverse the data label for the actuals (without changing the value of course).
Jan