Stacked Bar from this dataset config?

PJG · August 22

Not sure if this is possible directly, with Beast Mode, or if I have to change how the dataset is configured with ETL….

The dataset is currently setup like this simplified mockup:

Ignoring the fact that the numbers in my mockup, don't match my card mockup, I want to present them like this:

Is there an out of the box way to do this, and if not, what's my easiest/best option?

ArborRose · August 22

If you transform your dataset

Category	Value	Type
Budget	343	planned
Budget	3432	carry-over
Budget	123	unplanned
Forecast	444	planned
Forecast	4432	carry-over
Forecast	145	unplanned
Actuals	200	planned
Actuals	3302	carry-over
Actuals	80	unplanned

Then create a stacked bar using category on the xaxis, value on the yaxis, and type in series, you get this:

ArborRose · August 22

To transform your dataset, use Magic ETL.
Setup selected columns.
Add an Unpivot Tile. Drag the Unpivot Columns tile onto the canvas.
Unpivot Columns: Select budget, forecast, and actuals.
New Column for Key Names: Enter Category.
New Column for Values: Enter Value.
This will turn the three columns into two: Category and Value.

DavidChurchman · August 22

Ideally, your data would look like this:

Category Type Value

Budget Planned 343

Budget Carry-over 3432

…

The easiest way to get it in that format is with MagicETL. There's probably a more elegant way to do it with a dynamic unpivot, but to me, the easiest to understand is to just select the two columns, add a category column and stack the results on top of each other:

Once it's in that format, the stacked bar is easy:

ArborRose · August 22

If you transform your dataset

Category	Value	Type
Budget	343	planned
Budget	3432	carry-over
Budget	123	unplanned
Forecast	444	planned
Forecast	4432	carry-over
Forecast	145	unplanned
Actuals	200	planned
Actuals	3302	carry-over
Actuals	80	unplanned

Then create a stacked bar using category on the xaxis, value on the yaxis, and type in series, you get this:

ArborRose · August 22

To transform your dataset, use Magic ETL.
Setup selected columns.
Add an Unpivot Tile. Drag the Unpivot Columns tile onto the canvas.
Unpivot Columns: Select budget, forecast, and actuals.
New Column for Key Names: Enter Category.
New Column for Values: Enter Value.
This will turn the three columns into two: Category and Value.

PJG · August 23

Thanks @ArborRose - that did it; well explained, thank you!

Stacked Bar from this dataset config?

Best Answers

Answers

Categories