Forum Discussion
Multi-line line graph with multiple columns for the x axis
Hi all,
Quite new to Power BI. I am trying to plot some student results data (education setting) into a multi-line graph.
This is what my data looks like, it represents pass rates at different reporting periods (every 6 months, seperated into semester 1 and semester 2 for each year).
| Year Level | Subject Name | Faculty | 2024-Sem1 | 2023-Sem2 | 2023-Sem1 | 2022-Sem2 | 2022-sem1 |
| 1 | English | Primary | 79 | 76 | 81 | 68 | 76 |
| 1 | Maths | Primary | 81 | 78 | 78 | 76 | 75 |
| 2 | English | Primary | 88 | 87 | 85 | 84 | 82 |
| 2 | Maths | Primary | 79 | 79 | 78 | 81 | 82 |
| 7 | English | English | 88 | 87 | 88 | 87 | 85 |
| 7 | Maths | Maths | 75 | 75 | 77 | 81 | 83 |
| 8 | English | English | 80 | 80 | 81 | 80 | 79 |
| 8 | Maths | Maths | 65 | 72 | 77 | 79 | 81 |
| 10 | Biology | Maths | 65 | 79 | 88 | 79 | 80 |
Ideally, I would like a line for each row in the table (subject/year level combination), plotting the different semester data points. This will show me trends for each year level/subject. When I have tried, I can't seem to get it to display what I am after.
I have tried putting each of my data columns into the X-axis, and the subject in the X-Axis, but that defaults to a count of the subjects which is not what I want.
When reversing this, I get a count of the semester results instead of just displaying their values.
I found a reference to Unpivot in my googling, but that didn't seem to help.
Any help is appreciated.
Thanks.
Hi TrisBask ,
Thanks for amitchandak reply.
Based on your description, you want to create a line graph with multiple line segments, and those segments need to contain every piece of data. Since you want to show the trend of the data at different stages, you need to use the Year-Period field as the X-axis, so you need to turn these field names into values in the columns, where Unpivot is used.In Power BI line charts, we can only show categories in one field. So, we also need to use the Small Multiples field. Here are the detailed steps:
First, we need to merge columns in power query, hold Ctrl to select the Subject Name and Faculty fieldsAfter creating the merged column, hold Ctrl to select all Year-Period fileds and unpivot them
Close and apply and then you can create a line cahrt, you can slide the right slider to see the line graphs for different Subject Name-Faculty
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
- amitchandak
Super User
TrisBask , In power query: Unpivot all year-semester columns, and then split the new column with year-semester using split by delimiter into 2 columns. The format that you will get now should work
Power BI: Power Query Functions one must know: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=36407s
Hi TrisBask ,
Thanks for amitchandak reply.
Based on your description, you want to create a line graph with multiple line segments, and those segments need to contain every piece of data. Since you want to show the trend of the data at different stages, you need to use the Year-Period field as the X-axis, so you need to turn these field names into values in the columns, where Unpivot is used.In Power BI line charts, we can only show categories in one field. So, we also need to use the Small Multiples field. Here are the detailed steps:
First, we need to merge columns in power query, hold Ctrl to select the Subject Name and Faculty fieldsAfter creating the merged column, hold Ctrl to select all Year-Period fileds and unpivot them
Close and apply and then you can create a line cahrt, you can slide the right slider to see the line graphs for different Subject Name-Faculty
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
