Locked lesson.
About this lesson
Combination charts combine line and column charts into a single visual. They are very useful for showing values versus a standard or target.
Exercise files
Download this lesson’s related exercise files. You can download the source data files for the course from the resources section of your Lessons page.
Combination Charts.pbix454.5 KB Combination Charts - Completed.pbix
456.1 KB
Quick reference
Combination Charts
Building Combination charts in Power BI.
When to use
Combination charts are useful for showing values versus a standard or target.
Instructions
There are two types of Combination chart visuals included in Power BI:
Types of Combination Charts
- Line and Stacked Column chart
- Line and Clustered Column chart
Layout
- Drag your Field into the Axis area. The unique items in this field will be used for the primary axis
- The alternate axis is calculated automatically
- The measure(s) to use for the column heights should be dragged into the Column values area
- The measure(s) to use for the line should be dragged into the Line values area
- Segregate your columns (stacked or clustered) by dragging a field into the Column Series area
- Legends and tooltips can also be configured
Formatting
- Dragging multiple fields into the Axis area will activate Drill Down
- Visual formatting can be done via the fields under the paint roller in the Visualizations area
- Trend lines can be found under the magnifying glass icon in the Visualizations area
Hints & tips
Common Modifications for Combination charts
- Rename the Legend and force it to bottom
- Provide a custom title for the chart
- Change the y-Axis display units
- Force the y-Axis to plot on the primary axis by turning off y-Axis -> Show Secondary
- Change the line (or fill) colors
- Turn on Data Labels and customize the series (each series can be controlled separately)
- 00:04 In this video, we're gonna look at combination charts.
- 00:08 Which is essentially where we have a combination of column and
- 00:11 line charts together.
- 00:13 Power BI has two of them.
- 00:14 They've got the line and stack column chart, and my personal favorite,
- 00:18 which is the line and clustered column chart.
- 00:19 Now I'm gonna click on this one.
- 00:22 Generally you'll find that I tend to use more of the clustered column charts.
- 00:25 I don't like my data series stacked on top of each other in bars.
- 00:28 I like to see them next to each other because then I can see what's actually
- 00:31 going on with them.
- 00:31 You may have picked up on that from what I've built so far.
- 00:34 The first thing I'm gonna do, is I'm gonna for my sales and I'm gonna grab that and
- 00:38 put this onto my column values.
- 00:40 I'm then gonna go and
- 00:43 grab from my calendar field, my end of month, put that under the shared axis.
- 00:48 It aggregates into a date hierarchy, so
- 00:50 you can tell that I can use drill down on this chart if I want.
- 00:53 You can see all the icons there.
- 00:54 But, that's not what I'm looking for, I'd like to see this by end of month.
- 00:58 And I've now got a nice little chart that actually shows
- 01:01 some of the stuff that i am looking at, which is kinda cool.
- 01:04 Now what if I wanted to go and show something else, like Budgets.
- 01:09 Well budgets work on the same axis type scale, same values, so
- 01:12 this is a no brainer to add, again to the column values on this chart,
- 01:16 because I can actually see and compare the trends side by side.
- 01:19 So this all works for me nicely.
- 01:22 I'm not a big fan of the colors on it though, they're the defaults.
- 01:24 But you know what? I've already formatted on one of my other
- 01:27 pages into the colors I like, so why not go and grab that chart,
- 01:31 go to format painter, come back over to page two here, and
- 01:35 click on my chart to inherit all these things?
- 01:37 And yet you know what?
- 01:38 Those data labels, that's just way too much.
- 01:41 So I'm gonna go back and turn those off.
- 01:44 Here we are. Data labels are off and
- 01:45 we'll turn the Y axis back on.
- 01:47 Now you've seen this kind of a chart before.
- 01:50 Maybe not in these exact colors, but
- 01:52 this looks pretty similar to what we've built in the past.
- 01:54 But what we wanna add now, is I'd like to see,
- 01:58 while it's great that I can mouse over each data point, see what the sales are,
- 02:01 I'd really like to know what the trend of units is actually doing.
- 02:05 So, this is where we can make use of the column charts, or
- 02:09 the combination chart aspect of this particular piece.
- 02:12 So I'm gonna grab here, go to our field list.
- 02:16 Just collapse these out of the way for a second, and we're gonna scroll down near
- 02:19 the bottom, we'll find units and drag that onto the line values.
- 02:26 Now you'll notice that we get a thin red line showing up way down the bottom.
- 02:30 Now the reason for
- 02:31 this is because it's actually showing the units on the same scale.
- 02:34 So as you can imagine, with all of our units,
- 02:37 37,000 being sold because they go for much higher prices, the volumes are way higher.
- 02:41 So this line is really really small compared to what we actually need.
- 02:45 I'd love to see this more representative of what's actually going on.
- 02:49 In order to do that I actually need to change the axis that this is plotted on.
- 02:52 I need to plot it on a secondary axis.
- 02:55 So I'm gonna come over to my format painter, and I'm gonna go
- 03:00 down into the Y axis options and you'll notice that when you start working in
- 03:04 the screen here that's a little bit small, you'll run into some challenges here.
- 03:08 So I've got a scroll bar that will scroll me through some of the options and
- 03:11 it's not exactly obvious, but you see the half a box down here?
- 03:15 Well there's a secondary scroll bar in here.
- 03:17 And if I go and
- 03:18 scroll that, I get the option to play around with some other things.
- 03:22 One of those items, if I just open this up a little bit more is show secondary.
- 03:26 What this will do, is it actually, when I click it watch what it does to the charts.
- 03:31 It will actually change the charts quite drastically,
- 03:33 because now we get secondary access put into place.
- 03:36 So for the line series, it will run based on the secondary access.
- 03:43 If I go and shrink this back down a little, we will see the chart can expand
- 03:46 a little bit more, you can see what's happening here.
- 03:48 Now, am I happy with it?
- 03:50 I can probably make some more changes but at the end of the day,
- 03:53 I think this is a pretty good use of a chart.
- 03:55 We can actually see that we have got some different values for our sales and
- 03:58 budgets and we can see that the units are actually there showing in a different way.
- 04:02 Maybe I don't want to use that.
- 04:03 Maybe I would rather have a trend line for
- 04:05 my budget rather than show the units at all.
- 04:07 Well that's easy enough.
- 04:08 I can take units off, and I can just move budgets down onto the line values.
- 04:13 Now, at this point, I don't really need a secondary access,
- 04:16 because these things are the same.
- 04:17 As a matter of fact, I probably shouldn't have on here.
- 04:19 So, I can go back and say, let's go back to the Y-axis.
- 04:23 And let's actually take the secondary access and
- 04:26 turn it off to put the trend back into the right place.
- 04:30 We'd probably choose different color from my lines but
- 04:33 at this point you can see that we can actually start combining this charts
- 04:36 together into creative ways that work quite nicely.
Lesson notes are only available for subscribers.