Stacked Bar Waterfall - problems with Measures and Calculated Columns

[ghdunn]

I would like to create a Stacked Bar Waterfall.

I can spoof a simple version in this way:

Month Name Hard Coded Count

1 Jan	Bob	1
1 Jan	Fred	1
1 Jan	Mary	1
4 Feb	Bob	1
4 Feb	Fred	1
4 Feb	Anne	1
3 Leavers	Mary	1
2 Joiners	Ann	1
3 Leavers	1. Invisible	3
2 Joiners	1. Invisible	3

Which allows me to create this 'spoof' because I have turned the 'invisible' value white.

However, this is hard coding the value for the 'invisible' value.

If I wanted the count to be dynamic, then I can calculate the invisible value in M Query by using Group:

 #"Grouped Rows" = Table.Group(#"Changed Type", {"Month"}, {{"NewCount", each Table.RowCount(_), type number}})

I then bring that value for Jan and Feb into another step that generates the value for invisible...so far, so good.

HOWEVER....this only works for the relatively simply dataset I have.  

If I wanted to get smarter, and have a way of calculating the 'invisible' value even if the data is being filtered in the vizualisation as follows:

Month Name Type

1 Jan	Bob	Permanent
1 Jan	Fred	Temp
1 Jan	Mary	Permanent
4 Feb	Bob	Permanent
4 Feb	Fred	Temp
4 Feb	Anne	Permanent
3 Leavers	Mary	Permanent
2 Joiners	Ann	Permanent
3 Leavers	1. Invisible	x
2 Joiners	1. Invisible	x

I thought I could then use Measures to calculate how many people were in each month:

Jan = CALCULATE(COUNTROWS('Table'),'Table'[Month]="Jan")

Feb = CALCULATE(COUNTROWS('Table'),'Table'[Month]="Feb")

and then bring those measures back into the table using a calculate column to calculate the count

Generated Count= switch(true(),'Table'[Month]="Joiners" && 'Table[Type]= "x",[Jan],'Table'[Month]="Leavers" && 'Table[Type]= "x",[Feb],1)

Then that would generate a table:

Month Name Type Generated Count

1 Jan	Bob	Permanent	1
1 Jan	Fred	Temp	1
1 Jan	Mary	Permanent	1
4 Feb	Bob	Permanent	1
4 Feb	Fred	Temp	1
4 Feb	Anne	Permanent	1
3 Leavers	Mary	Permanent	1
2 Joiners	Ann	Permanent	1
3 Leavers	1. Invisible	x	3
2 Joiners	1. Invisible	x	3

Which would be filterable in the vizualization :

So when I remove 'Temporary' from the filter, it dynamically re-calculates the invisible values for Jan and Feb.

But the combination of Measures and Calculated column doesn't seem to work.

If anyone could put me on the right track?

[sturlaws]

Hi, @ghdunn 

As you have experienced, calculated columns does not recalculate on any filter or slicer input. If you want this kind of dynamic behaviour, you will have to do it all by measures.

Could you provide a relevant sample data set, without any hardcoding?

Cheers,
Sturla

[ghdunn]

Hi Sturla,

Thanks for responding.

I think that is a simple sample data set in the OP - I don't think it needs to be any more complicated than that.

I am trying to visually compare headcount one month over another and show how many people have joined, and how many have left.  I could do that with a normal waterfall if it was one dimensional.  However, I want to be able to visualise different groups of 'Types'.  To do that in a waterfall style requires me to calculate how many people were in there in Jan, and then in Feb.  Again...that is no problem using CountRows.  However, I haven't worked out how to write another measure which acts on each row of data.  Hence my use of Calculated Column which as you say is bogus.

What would the syntax of a measure be that replicated the logic of my effort, but did so in a measure?

Gerald

[sturlaws]

Ok, I finally got what you are trying to do. But. Will your data only contain data for january or february? Because hard coding this works in a simple data set like this, but what happens when you have 2 years worth of data? How should it be displayed?

[ghdunn]

That is the point...I don't want to hard code it.  I have hard coded it to start with simply to show what I want to display, but my ideal solution is to use Measures so that the visualization is dynamic.  I included some simple pictures in the OP of what I want it to look like...do they display for you?

Being able to compare 2 months would be a start...the approach I was trying to pursue using Measures and a calculated column could be scaled Jan, Feb, Mar etc and then use a SWITCH statement...not very elegant I know but workable.  But that is moot given the constraints of Calculated columns. 

Obviously a fully scalable, elegant solution would be ideal but I will take what I can get for the moment.

Is there some Measure command that allows me to say something like:

Measure=IF(Month = "JanJoin" and Type = "x" then [Jan], else if Month = "JanLeave" and Type = "x" then [Feb], else if....   etc etc

or

Measure= SWITCH(TRUE(), Month="JanJoin" && Type = "x",[Jan],Month = "JanLeave" && Type = "x",[Feb],

one problem I can see is that using the conditional test "Month =" is looking up a 'naked' column.

[sturlaws]

do you need have the employees as legend?

[ghdunn]

No Sturla...not necessarily.  However I would want to be able to use Type as a legend. 

Thanks for the time you are taking on this...much appreciated

Gerald

[sturlaws]

So, here is a draft: pbix

I have added data for march as well. In power query a new table is generated, so that the values that goes on the x-axis is dynamically handled.

And the measure is dynamic as well. The DAX of the measure might look a bit scary, but it is not very complex DAX. 

[ghdunn]

[sturlaws]

new version: pbix 

This is as close as I can get, had to make use of a dummy Type, x, in order to get new and lost up on the Y-axis.

Cheers,
Sturla

[Anonymous]

Please refer https://github.com/apanasara/Multiple_Waterfall_StackChart_PowerBi

[ghdunn]

Sturla, Thanks for putting in so much effort. My hope was that when filtered on a type, the 'invisible' value would re-calculate so that the Joiner/Leaver bar would start from a different place. So by selecting Permanent, the New would show as raising the total from 2 to 3 and not the original 3 to 4. Will understand if you feel you have spent enough time on this already!! Gerald

[sturlaws]

good point that the chart should work when filtering for type. But, to do so I would need to add yet another stand-alone table, with even more code, and I am not sure it would even work. What you can do instead, is to have three versions of your table. 1 with both types, as you have now, and 2 tables containing only Permanent and Temp, respectively. Duplicate the measure in each table, and create 3 identical charts. Then instead of a slicer, create buttons and bookmarks to switch between them.

[ghdunn]

Sturla,

Thank you for your outstanding effort on this - way beyond the call of duty.  It will take me a little while to work through your solution and see what I can learn and I will let you know where I get.

Thanks once again!

Gerald

[ghdunn]

Sturla,

Many thanks.  Could I just clarify, though that we are on the same page.

One of the major issues I am trying to deal with is to make the chart look like a waterfall chart, to show changes one month to the next.

So...if the first month has say, 5 people, and the 2nd month has two people added, then the chart would look as follows: