Waterfall visual from several measures

[baribir]

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!

[Anonymous · Accepted solution]

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 

[Anonymous · Accepted solution]

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 

[baribir]

Hi @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.

[Anonymous · Accepted solution]

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 

[baribir]

Hi @Anonymous ,

I have calculated YTD values and I have tried to do the same waterfall but the data shows as it is actual. Why YTD values are not calculated inside summarize function?