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

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. 

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    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'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:

     

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

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    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

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

    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