Line chart using a table that has multiple fields for x axis

[megavic]

Hi - new Power BI adopter here!

I have a table that I'd like a simple line graph for. The issue I have is that the values for the x axis are in individual fields in the table, e.g. PeriodBalance1, PeriodBalance2 etc. being Jan, Feb...

'Year' is in a single field though.

The requirement is x axis showing the months, Y axis is the value and legend being year.

The x-axis only accepts 1 field, is there a clever way round this in Power BI?

My description is probably not too clear, screen print below of the fields - thanks in advance:

[v-yuezhe-msft]

Hi @megavic,

Create a calendar table and import it into Power BI Desktop, then create relationship between the calendar table and your current table using Date Key. This way, you are able to drag Month from calendar table to Line Chart. You can check the example in the attached  PBIX file to get more details.

Thanks,
Lydia Zhang

[v-yuezhe-msft]

Hi @megavic,

Based on your description, it seems that you want to display multiple fields’ values in different years and different months using Line chart. In Line chart, when you put Month into X-axis and put Year into Legend, you are only able to drag one field into Values. The visual will look like as follows.


Moreover, in Line chart, when you put Month into X-axis and drag multiple fields into Values, you are not able to drag Year into Legend, please check the following example for more details.


In your scenario, Matrix visual is more appropriate.



Thanks,
Lydia Zhang

[megavic]

Hi Lydia,

Many thanks for your speedy response!

I understand everything you've said, and I am already showing the data in a matrix - I did however want to show the data at the same time in a simple line graph. I'm thinking I could perhaps pull all the data in to a single field in access. Is there anything I could do in power BI query to move the data in to a single field?

[v-yuezhe-msft]

Hi @megavic,

You can consider to create a calculated column to sum the values of these fields, then create Line chart by putting Month into X-axis and dragging the new column into Values and dragging Year into Legend.

For example: column = Test[Period1]+Test[Period2]+Test[Period3]+Test[Period4]+Test[Period5]+Test[Period6]+Test[Period7]




Thanks,
Lydia Zhang

[megavic]

Thanks @v-yuezhe-msft,

I think the issue with I have with that idea is that I don't have a 'month' field.

I'll have a look at what I can do to manipulate the data before it gets in to Power BI.

Thanks again,

Andy

[Anonymous · Accepted solution]

Ya, the "shape" of your data is not helping you.  In the query update window thingy (power query) -- check out the UNPIVOT feature, that would change your life.

[megavic]

Oh that is sooooo good 

That completely reshapes the query table data and is so simple but powerful!

Thank you very much for the suggestion, this is working perfectly.

Much appreciated - kudos and accepted answer 

[Anonymous]

When you flip to different visuals, you will see different value/filter/legend options.  I suspect if you just click the line graph visual... you will see what you need?

[megavic]

Hi scottsen, thanks for your reply.

When I click the line graph visual I can't see how I can use each period balance field to appear as an entry on the x-axis.

Any help appreciated

Andy