Hello,
I am working with waterfall chart of batch cycle time productions.

I was able to create the high level chart with Deneb, but I am struggling with a modified version where I am including substeps in, and where I would like to see them stacked on top of a step, colored by criteria, where the waterfall chart should still be ordered by main step. I have attached a PowerBI file with the 2 examples, and a sketch of how the final viz should look like… Cycle Time Waterfall Example.pbix (1.5 MB)

I’ve spent a while investigating your issue, but unfortunately haven’t come up with anything (likely as I can’t seem get my head around your data model … is the filter direction correct? What is the dimension [step or batch] and what is the fact table?).

My first thought was that an avenue to pursue was 2 overlapping bar charts, one as you have it with the aggregate of the steps and one with the individual steps, coloured as you note.

To go forward, perhaps a simple table that displays all the raw data you’d like to see in the visual would help. Then, the calculations (aggregates, averages) could be added as transforms inside Vega-Lite.

Hi @Greg,
sure! Cycle Time Waterfall Example.pbix (1.5 MB)
revised.
The filter direction is correct, I have called the tables d- for dimension and f- for fact.
The fact table is the one containing the batch and duration, the table with step and substep is the dimension.
I have stripped the needed data to a bare minimum in a matrix below the chart, so that the idea of what I would like to achieve should be clearer. The color should be given by the criteria “Is Wait?” which is applied at substep level.

Think about batches as transactions - the batch number is like a customer order number, the duration is like the amount. Step and Substep can be the equivalent of geographical area and country.

Hope it makes sense… and thanks a lot for looking into this!
Kind regards
Valeria

(to specify a little more: in real life the step and substep are encoded in the fact table to improve performance, I have cut this part for simplicity in the example)

Thanks for the table and explanation. I made some progress when starting from scratch and Vega-Lite rendered the stacked column chart automatically. I added a stroke color to see the substeps more easily, a simple joinaggragate transform to get and rank the step total, and a layer block to add both the column and the column label (filtering for only 1 value for each column label to be added; sorry, time constraints).

None of this answers your question, unfortunately, as the penny hasn’t dropped yet for me to get the correct transform that will properly calculate the starting Y value for each stack. Nevertheless, I’m posting what I have so far in case its useful. I need to move on to other things for the rest of the day, but will try again tomorrow and will post if I find anything.

Thanks a lot @Greg !!! I really appreciate the time you are spending on this.
At least I feel relieved it’s not so evident - I have been looking for a solution by myself for days…

In case this might be useful to others - here is the solution.
Thanks to all who contributed - @Greg , @dm-p , and Madison Giammaria! Cycle Time Waterfall Example.pbix (1.5 MB)