Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to 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.
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
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
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?
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
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
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.
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
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?
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