How to combine a clustered and stacked chart in Power BI

Posted On 2022-02-04

Let’s say you have a total budget, you want to compare it to your 3 income streams and you want to merge the diagrams below into one. Clearly it’s possible (I haven’t made the final diagram in paint you know) and here’s how it’s done!

The issue

In Power BI, there are these 2 types of bar charts that are very commonly used. One is called a stacked bar chart since the values are stacked on top of each other and the other one is called clustered where you get the values next to each other.

Let’s say you sell stuff and have 3 categories. Using a Stacked chart, it’s easier to see how much they all add up to on a given month, but using a clustered chart will make it much easier to see how the different income categories compare to each other.

On the left one, I can quickly see that we reach almost 10K in May, but it’s hard to see if Consulting or Services had the biggest impact. On the right side, I can much easier see the different and that Services amounted for more than Consulting in May.

Now let’s bring Budget into the mix shall we?

Since my data looks like this, let’s first create a measure that simply combines our income streams into one measure.

The measure, for the sake of this blog post, simply adds the columns together like this:

I’m using a clustered chart to show how the budget and the actual outcome compare month by month and it’s now easy to see our result.

So far so good but the headline clearly says issue and so far, we’re doing great!

Here it is. If you have a stacked column chart and you add the Budget value, it’s going to be stacked. Duh..

This doesn’t help us at all!

Making it a clustered chart isn’t the solution, we only want the Budget and the rest of the incomes to be separated. This is the same graph as above, except clustered.

The solution I’ve made (and I’m 100% sure there are much better ones out there so feel free to keep searching or use my solution as a base to improve upon) is actually just a Stacked column chart, but I make us of the X axis to separate the budget from the income. Here’s how it’s done!

The solution

Introducing the chart table! It’s manually added data to a table with just 1 column. The rows are my category names. I called them Types here for some reason.

In my new table, I created 1 measure per column of values, like this:

_Chart Budget = 
SUMX(Chart_Table,
    VAR CurrentType = Chart_Table[Type]
    Return
    
    IF(
        CurrentType="Budget",
        SUM('Table'[Budget])
    )
)

All the measures are the same except for this part

For product, Services and Consulting I sum the column if I’m currently on the row named Income and for the Budget, the row must be budget.

Finally, create you stacked column chart but add Type as the lowest level of the Axis. Make sure you show all levels of the chart. Since there is no relationship between the 2 tables, Power BI will add the row values to each axis over and over (which is what we want). When the budget is calculated it will skip the axis that says Income and vice versa. This gives us the outcome we want!

If you prefer, you can download this sample report from github here!

If you make improvements or have any kind of questions, feel free to reach out to me at https://twitter.com/Villezekeviking or https://www.linkedin.com/in/ville-gullstrand-mvp-b355067a/

Cheers!