7 Secrets of the Line Chart

The line chart is the go-to chart type to visualise data over time. Typically, this visual is used to analyse your data by year, quarter, month or by day. It should be an easy thing to do to create a line chart that plots your data in the way you want. But beware, there are secrets lurking behind the benign line chart! Here are my top seven.

#1 There Are Two Different Types of X-Axis

The default x-axis, along the bottom of the chart, is what’s called a “continuous axis” and you’ll think that’s how the x-axis always has to look. But the secret is that there are actually two types of x-axis; continuous and categorical. To show the difference between these two types, let’s start with a simple line chart that uses a date column (not a date hierarchy which we look at below). In the example of a line chart below, we’ve used the SALE DATE column in the Axis bucket:-

By default, you’ll get a “continuous” axis which is only applicable to numeric data, like dates and times. You’ll see that the SALE DATE is sitting on the x-axis but we only get months and years showing, not the individual dates that are being plotted. We have no control over the scale on the continuous x-axis. For example, if we extend the date range and/or change the size of the chart, the scale automatically adjusts. Firstly, we’ll get every month, then every other month and eventually every six months:-

You’ve got no control over the format of the dates in a continuous axis. If I don’t like the short month names, too bad.

Clearly it makes sense to have the continuous x-axis as the default as it would be difficult to label every data point when you have many dates to plot. However, if you have a smaller date range e.g. a week’s worth of dates, you may want a label for every date. If this is the case, you can change the x-axis to a “categorical” axis by using this option on the X-axis card on the paint roller:-

A categorical axis can use text or number values but unlike the continuous axis, it always labels every data point.

If you’re using a categorical axis and your own date
column, unlike the continuous axis, you can format the date any way you want. You can either format the date column in your table using the “Format” button on the Column tools tab:-

Or, if you want a special format, you can create a calculated column in the table where your date is and use a DAX expression like this one:-

SALE DATE V2 = FORMAT( Sales[SALE DATE], “dd mmm yy” )

Note: the table that holds the date is called “Sales”

In the above expression, the “d”, “m” and “y” are placeholders for how you want to format the day, month and year respectively:-

You can then use your new date in the Axis bucket

Mostly however, you’ll probably be using the default date hierarchy that’s created for you by Power BI. If you’re using the default date hierarchy, you can drill down to quarter or month granularity by using “Expand all down one level in the hierarchy”.

A date hierarchy in the Axis bucket	You can drill down to different granularities
Image	Image

Just like when using a date column, by default you’ll be given a continuous x-axis as you drill down. But again, you can change this to a categorical axis if you prefer. For example, you can have a line chart at month granularity with either of these axes types:-

Using the default Date Hierarchy you can change the Axis type
Image
Continuous Axis at Month Granularity		Categorical Axis at Month Granularity
Image		Image

So remember, there are two x-axes from which you can pick, so you can design the right line chart that is applicable to your data.

#2 When Using a Default Date Hierarchy You Can’t Change the Date Format

This is the secretive difference between using a date column and using the default hierarchy. You can’t change the format of the categorical axis if you use the hierarchy, which is a shame because when you drill down to month granularity, you’re lumbered with a long month names that just takes up space:-

If you want to use the hierarchy but to also have your own formats for year, quarter and month, you will need to create your own separate columns, and this is my next secret!

#3 How to Format Year, Quarter and Month on the X-Axis

The secret to formatting the x-axis the way you want when using a hierarchy, is to create separate columns for year, quarter, month and month number alongside your date. You can format them in the way you want and so control how they look on the x-axis:-

Note: Month number is required to sort the month names, using the “Sort by column” button.

You can use Power Query to generate these columns. You can then use these columns in the Axis bucket of the line chart, creating a hierarchy of your own that you can drill down. Use the “Expand all down one level in the hierarchy” button to drill to different granularities. At last we’ve been able to get a short year and short month names:-

You may find the line chart is sorted by the values rather by your dates. You can change this by clicking on the “More Options” button and selecting the correct sorting options:-

However, there is one drawback to creating your own hierarchy. Because the quarter and the month labels are text values, you can only plot them on a categorical axis. The secret to achieving a continuous axis for quarter and month follows….

#4 How to Get a Continuous Axis for Quarter and Month

If you’re working with you own date hierarchy and you drill from year to quarter or to month granularity, the x-axis changes from “Continuous” to “Categorical”. This is because the quarter and month columns are text values and you can only have a continuous axis with a numeric column, such as year. Also the x-axis labels are concatenated by default for the categorical axes:-

Year granularity has a continuous x-axis	Quarter or month granularity have a categorical x-axis
Image	Image

We’ve already seen how we can turn off the concatenation for the categorical axis but even then, it’s so annoying to always have to have every data point labelled! So what is the secret of getting a continuous axis for quarter and month?

To let you into the secret, there are two things you’ll need to do. Firstly you’ll need to have a separate date table, where you’ve created your date hierarchy. Or at least you’ll have to make sure that Power BI doesn’t create date hierarchies for you. You can turn off the automatic creation of date hierarchies in the Power BI Desktop Options, under “Data Load”:-

The second thing you’ll then need to do is to create separate columns for quarter and month that generate dates and not text. If you have your own date table (or you’ve turned off “Auto date/time for new files”) you can create these two calculated columns in addition to your Year, Qtr and Month columns. You can then use these in the Axis bucket instead of the hierarchy.

Year-Month = CALCULATE (
MIN ( DateTable[DateKey] ),
ALLEXCEPT ( DateTable, DateTable[MonthNo], DateTable[Qtr], DateTable[Year] )
)

————————————————–

Year-Quarter = CALCULATE (
MIN ( DateTable[DateKey] ),
ALLEXCEPT ( DateTable, DateTable[Qtr], DateTable[Year] )
)

A continuous axis showing month using a calculated column to generate a date	A continuous axis showing quarter using a calculated column to generate a date
Image	Image

#5 Turn Off Concatenation to Get a Multiple X-Axis

If you have a categorical x-axis, the default is to concatenate your labels. This is where the labels of the date hierarchy are strung together as you drill down e.g. “2018 Qtr 1 February” is Year, Qtr and Month. Often you want to turn it off because it looks very ugly and takes up too much room. You probably want a multiple x-axis like the one you get in Excel line charts:-

What is the secret to getting the line chart to look like the Excel one? Well, the answer lies in turning off the concatenated labels by using the slider on the X-Axis card:-

Concatenated Categorical X-Axis	Non-Concatenated Categorical X-Axis
Image	Image

Note: If turning off the “Concatenate labels” option doesn’t appear to work, here’s the secret; for some unknown reason you just need to re-sort by “Year Qtr Month” (see above) even though they are already sorted like this.

Beware that as soon as you turn off the “Concatenate Labels” option, you lose the ability to change the axis type back to Continuous. To be fair, it would be impossible to have a non-concatenated continuous axis, but it’s sometimes not obvious that this is why you can’t change the axis type.

Concatenation is on and you can change the axis type	Concatenation is off and you can’t change the axis type
Image	Image

#6 You Can Colour Each Data Point

It’s no secret that you can use the Shapes formatting card to turn on markers:-

But did you know that you can have each data point coloured differently? On the Data colors card, turn on the “Show all” slider and jazz up your line chart!

#7 Getting More Space on a Categorical Axis

If there’s not enough space for the labels on a categorical, concatenated axis, you can try using the “Maximum size” slider on the X-Axis card to give you more room:-

I hope you liked my seven secrets of the line chart. Would you have thought there were so many quirks to this humble visual?

Line charts in Power BI are extremely useful when it comes to displaying date information clearly and wanting to drilldown into the numbers. We cover a range of visualisations on both our Fundamentals and Advanced courses, meaning you can get in depth knowledge on exactly how to use these functions to your advantage.

If you would like more information about our courses, get in touch with our team on 0800 0199 746.

Blog