Waterfall chart with multiple measures

[IF]

Hi,

I read discussion about waterfall chart but it didn't work when I tried the following.

I have product category and 8 measures in the same table. The measures have + and - values. I would like show them in the waterflow chart. I made another new table (name:Waterfalltable); one column for the name of the measures and the other one is for index. I also made a new measure.

Waterfall =

SWITCH(

SELECTEDVALUE('Waterfalltable'[Index]),

1, +[R_DM],

2, +[R_Nego],

3, +[R_Pjt],

4, +[R_Raw],

5, +[R_FX],

6, +[R_EC],

7, +[R_Pjt2],

8, +[R_Raw2],

BLANK()

)

I am not sure what to do next at the visualization panel. I tried to put the new measure to value field didn't work.

Maybe there is a better way of doing waterfall.

Could you help me?

All  the best

After this stage I am not sure what to do. Also there should be a relation between the new table and 

[v-alq-msft · Accepted solution]

Hi, @IF 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Test:

There is no relationship between two tables: You may create measures as below.

A Measure = SUM('Table'[A])

R_DM Measure = SUM('Table'[R_DM])

R_Nego Measure = SUM('Table'[R_Nego])

R_Pjt Measure = SUM('Table'[R_Pjt])

Result = 
SWITCH(
    SELECTEDVALUE(Test[MeasureName]),
    "A",[A Measure],
    "R_DM",[R_DM Measure],
    "R_Nego",[R_Nego Measure],
    "R_Pjt",[R_Pjt Measure],
    BLANK()
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-alq-msft · Accepted solution]

Hi, @IF 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Test:

There is no relationship between two tables: You may create measures as below.

A Measure = SUM('Table'[A])

R_DM Measure = SUM('Table'[R_DM])

R_Nego Measure = SUM('Table'[R_Nego])

R_Pjt Measure = SUM('Table'[R_Pjt])

Result = 
SWITCH(
    SELECTEDVALUE(Test[MeasureName]),
    "A",[A Measure],
    "R_DM",[R_DM Measure],
    "R_Nego",[R_Nego Measure],
    "R_Pjt",[R_Pjt Measure],
    BLANK()
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[IF]

Hi,

Thanks for the answer. Is there any possibility to display the changes in an order? I would like to sort them "A", "R_DM", "R_Nego", "R_Pjt". I have more to measures. Therefore the order is important for me.

Best,

[v-alq-msft · Accepted solution]

Hi, @IF 

You may go to 'Query Editor', click 'Add Columns'=>'Index Column' to create an index column and then 'Close and Apply'.

Then you need to make the column 'MeasureName' selected, click 'Sort by column'=>'Index'.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[IF]

Hi,

I don't know if I am doing something wrong for sorting. As you mentioned,  I add the index column:

Index columnsorting

The sorting doesn't change at all. It is a small issue, but annoying me. Maybe I do smtg wrong.

All the best,

[aj1973]

Hi @IF

Not sure that understood your issue but i think it is because your Waterfall measure is returning a string instead of a Value and probably thats why the waterfall chart is not accepting it.

Try to use True() instead of SELECTEDVALUE inside your SWITCH and the number of your Index for the values.

Waterfall =

Switch(

True(),

'Waterfalltable'[Index]=1,+[R_DM],

.

.

[IF]

Hi,

For example; I have the following data. The listed are all measures.

XX	YY	A(this is measure 1)	R_DM (this is measure 1)	R_Nego(this is measure 2)	R_Pjt (this is measure 3)
35353	2M	250	3	-10	-10
35353	3M	200	4	10	-5
35353	6M	250	3	10	20
35355	1M	300	4	-10	-5
35355	2M	200	6	-10	5
35355	3M	300	5	0	-15
35360	1M	300	7	-5	-10
35360	2M	300	7	-5	10
35360	4M	300	6	0	10

Based on the xx category I want to show waterfall charts. The value should be sum for each xx category. Again all of the values are measures. The waterfall chart should show:

XX	YY	A(this is measure 1)	R_Nego(this is measure 2)	R_Pjt (this is measure 3)	R_Raw (this is measure 4)
35353		700	10	10	5
35355		800	15	-20	-10
35360		900	20	-10	10

I hope it is clear now.

All the best,