Forum Discussion
Helper IIIStacked Bar Waterfall - problems with Measures and Calculated Columns
Resident RockstarHi, 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
Helper IIIHi 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?