Forum Discussion

ghdunn's avatar
ghdunn
Icon for Helper III rankHelper 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 ...
Need Help
ghdunn's avatar
ghdunn
Icon for Helper III rankHelper III
6 years ago

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's avatar
sturlaws
Icon for Resident Rockstar rankResident Rockstar
6 years ago

do you need have the employees as legend?

  • ghdunn's avatar
    ghdunn
    Icon for Helper III rankHelper III
    6 years ago

    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's avatar
      sturlaws
      Icon for Resident Rockstar rankResident Rockstar
      6 years ago

      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's avatar
        ghdunn
        Icon for Helper III rankHelper III
        6 years ago

        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: