How do I get a single graph showing trend lines for each month

[Field-smith]

I am trying to create a graph which displays number of vendors selling x number of units in separate lines to show how sales are changing monthly. I can't get the data to display in a single graph, power bi keeps splitting it off into individual gaphs. Units sold on the X axis and number of vendors on the y axis.

I have the following data of units sold by number of vendors each month:

Units Sold	APR	MAR	FEB
0	0	0	0
30	900764	714575	605772
60	642	110	0
90	225	33	0
120	111	23	0
150	107	15	0
180	88	17	0

Can anyone advise how to get this to display as a single graph with separate lines for each month? All help is appreciated, thanks.

[Joe_Barry · Accepted solution]

Hi @Field-smith 

Is the data in your table formatted as a Pivot as you are showing it? You can create separate measures, but it isn't sustainable. You will need to unpivot your data. What is the Units Sold column? Are these the type of units or is it a range? I will ignore this column for the moment.

In Power Query. Please do the following

• Highlight all Month columns
• Right click on any of them and choose unpivot
• You should be left with Three columns. "Units Sold", "Attribute" and "Value". You can rename these if you wish
• You will need to create a date column. Add a custom column. 

if [Attribute] = "Jan" then 01/01/2024 else
if [Attribute] = "Feb" then 01/02/2024 else
if [Attribute] = "Mar" then 01/03/2024 else .....Finish for the year​

• If you have a year column in your data then change the code to

if [Attribute] = "Jan" then "01/01/" & TextFrom.Number([Year])​

• Convert to date
• Load the data into the report
• Create a line graph visual add the new date column to the X axis field and the Values column to the Values field

You can build out your date to Months, Year etc.. There is plenty of documenation online to show you how to do this

Hope this helps

Joe

[Uzi2019 · Accepted solution]

Hi @Field-smith 
Do you want something like this??

You have to pivot your table so that all your month will come under single column.

[Joe_Barry · Accepted solution]

Hi @Field-smith 

Is the data in your table formatted as a Pivot as you are showing it? You can create separate measures, but it isn't sustainable. You will need to unpivot your data. What is the Units Sold column? Are these the type of units or is it a range? I will ignore this column for the moment.

In Power Query. Please do the following

• Highlight all Month columns
• Right click on any of them and choose unpivot
• You should be left with Three columns. "Units Sold", "Attribute" and "Value". You can rename these if you wish
• You will need to create a date column. Add a custom column. 

if [Attribute] = "Jan" then 01/01/2024 else
if [Attribute] = "Feb" then 01/02/2024 else
if [Attribute] = "Mar" then 01/03/2024 else .....Finish for the year​

• If you have a year column in your data then change the code to

if [Attribute] = "Jan" then "01/01/" & TextFrom.Number([Year])​

• Convert to date
• Load the data into the report
• Create a line graph visual add the new date column to the X axis field and the Values column to the Values field

You can build out your date to Months, Year etc.. There is plenty of documenation online to show you how to do this

Hope this helps

Joe