Power BI Desktop Line Chart with Multiple Lines

Question

I'm trying to overlay three different columns in one chart on Power BI Desktop.

To try to show a comparison of the number of orders per month by different years I have one column for Order Number, and three other columns for Sale Date; Sale Date 2016, Sale Date 2017 and Sale Date 2018. For each order number the three Sale Date columns have a number from 1-12 (representing the month in that year that the order occurred) or a null value (if the order occurred in another year). Something like this:

I'm trying to overlay the three Sale Date columns as individual lines on a line chart with Order Number (Count Distinct) as one axis and Sale Date 1-12 as the other axis. I would think that this would be comparatively simple but Power BI Desktop doesn't seem to have an option to set this up. I'd be open to other types of chart too, as long as the different years can be easily compared.

Answer 1

The reason you're having trouble is that your data is not shaped in a way that is conducive to the way Power BI works.

I'd recommend unpivoting your Sale 201x columns in the Query Editor before trying to do anything. When you do this, your data will look like this instead.

Order # | Year      | Month
--------|-----------|------
101     | Sale 2016 | 6
102     | Sale 2017 | 8
103     | Sale 2018 | 12
104     |           |

When it's in this format you can use it in a visual much more easily using Order # on your axis and Month for values while using Year as your legend.