- PowerBI: Creating a stacked clustered column chart

A few Weeks ago the client asked me to change an existing stacked column chart. What the client wanted, was to see the stacked actuals and the stacked actuals last year. So in fact the client wanted a stacked clustered column chart. As we all know this isn’t a standard visual in PowerBI, so I had to come up with a solution.

The final result is made, with 2 standard stacked column chart visuals on top of each other. The final result you can see in the pictures below. To make it more complex my client needed to plot the chart by week or by month.

This change of time context is made with buttons and bookmarks above the charts. Also within the weekly variance a horizontal scrollbar isn’t working, because of the layered charts. That is why, I have added buttons to navigate by a set of 1-18, 19-36 or 37-52 weeks.

Scroll down for a detailed step by step guide on how I achieved this and maybe there is something useful for your own PowerBI journey.

Let me know what you think or if there would be other possible solutions.

Step by Step Guide

Step 1:

Create a normal stacked column chart. In this case it’s a Stacked Column Chart with current year planned holiday hours stacked with taken holiday hours on top of the planned hours by month. Make sure, the option ‘show items with no data’ is on, to always show every item on the X axis.

Step 2:

Remove all settings like gridlines, background and legend. Why we do this, will be explained in step 8. Set the spacing inner padding of the columns to a maximum of 50 to create a gap between the columns.

Step 3:

Hide the first chart in the selection panel. Now create a similar chart, but now with the same data from last year. Also in this chart, make sure the option ‘Show items with no data’ is checked. Same as in the first chart, set the spacing inner padding of the columns to a maximum of 50 to create a gap between the columns.

Step 4:

In this second chart, remove the legend, and the background. Secondly make the chart title and axis values transparent, by using a custom measure (Transparent = "#FFFFFF00"). Apply this measure in the FX option of the chart values and chart title text.

The result will be a chart with only stacked columns.

Step 5:

To make sure the Y axis on both charts have the same height and both start at 0, we have to set the minimum and maximum for the 2 created charts. For the maximum we have to create a measure, which will determine the max value from both current year actuals and values from last year.
The measure I created:

Max Y-axis Holiday over Time =
VAR MaxHoliday_Act = MAXX(
ALLSELECTED('DML dimDate_Fiscal_Period_Month'),
CALCULATE([Holiday_Act])
)
VAR MaxHoliday_Act_LY = MAXX(
ALLSELECTED('DML dimDate_Fiscal_Period_Month'),
CALCULATE([Holiday_Act LY])
)
RETURN
IF(MaxHoliday_Act >= MaxHoliday_Act_LY, MaxHoliday_Act, MaxHoliday_Act_LY)

When the measure is created, we can now set the minimum and maximum Y axis on both charts. The maximum is set by using the FX button and select the new calculated measure.

Step 6:

Add a shape without borders with the desired background for the chart. In my case this is a white background.

Step 7:

Now we can start building our custom chart by overlaying the different visuals in the desired order.

What we see, is that by the order in the selection panel our data from last year is behind the columns of the current year. We can now use the visual options to position our last year chart in a position we desire, so we can see the stacked columns.

In the general options of the visual, go to ‘position’ and change the ‘horizontal settings’, until you get your desired effect.
In this example, I went for a horizontal position difference of 10px between both charts.

Step 8:

In step 2 and 4 we have removed the legend from the charts. Because we use two charts we have to create our own legend with the blue and grey coloring. So we create a custom legend, by using a textbox for example or other visual solution, to inform your user. In my case, I used text boxes to create a legend and plotted it on top of the chart combination.

Now this chart is ready for use.
(Tip: Combine all the visuals used, into a group in the selection panel, so you can quickly move it around the canvas).

As mentioned earlier, the client also wanted a variance by week. So I repeated the steps 3 times to create 6 charts. 3 sets of two, where the first set contained: weeks 1 till 18, the second set: weeks 19 till 36 and the third set: weeks 37 till 52.

With buttons and bookmarks I created a navigation on top of the chart.

I hope this was useful and thank you for taking the time to read it.

PowerBI: A stacked clustered column chart