I would like to create a Stacked Bar Waterfall. I can spoof a simple version in this way:Month Name Hard Coded Count1 JanBob11 JanFred11 JanMary14 FebBob14 FebFred14 FebAnne13 LeaversMary12 JoinersAnn13 Leavers1. Invisible32 Joiners1. Invisible3 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 Type1 JanBobPermanent1 JanFredTemp1 JanMaryPermanent4 FebBobPermanent4 FebFredTemp4 FebAnnePermanent3 LeaversMaryPermanent2 JoinersAnnPermanent3 Leavers1. Invisiblex2 Joiners1. Invisiblex 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 JanBobPermanent11 JanFredTemp11 JanMaryPermanent14 FebBobPermanent14 FebFredTemp14 FebAnnePermanent13 LeaversMaryPermanent12 JoinersAnnPermanent13 Leavers1. Invisiblex32 Joiners1. Invisiblex3 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?

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.

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

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

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?

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 etcorMeasure= 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.

ghdunn

Helper III

6 years ago

Stacked Bar Waterfall - problems with Measures and Calculated Columns

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?

Need Help

14 Replies

sturlaws
Resident Rockstar
6 years ago
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
  Helper III
  6 years ago
  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
    Resident Rockstar
    6 years ago
    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?

Forum Discussion

Stacked Bar Waterfall - problems with Measures and Calculated Columns

14 Replies

Related content

Problem with calcul

Calculation problem

Waterfall Chart

Waterfall Chart Help

Logic problem?