Forum Discussion
How to build a line chart with multiple lines from dynamic table/ measures
Hi,
I am currently building an automation with Power BI from an Excel file like below:
| Sales | 2022-2023 | 2023-2024 | 2024-2025 |
| Q1 | 63 | 74 | 113 |
| Q2 | 14 | 35 | |
| Q3 | 24 | 178 | |
| Q4 | 100 | 68 |
base on the sales data we could get a cumulative table:
Q2 YTD = Q1 sales+ Q2 sales ... etc.
| 2022-2023 | 2023-2024 | 2024-2025 | |
| Q1 YTD | 63 | 74 | 113 |
| Q2 YTD | 77 | 109 | 113 |
| Q3 YTD | 101 | 287 | 113 |
| Q4 YTD | 201 | 355 | 113 |
Select the table and insert a line chart
I am having issue on rebuilding this into Power BI since a line chart needs a measure for each line, I will have to build a new measure each year which isn't sound like a good plan. Wondering if we have a way to build a line chart ( or other charts if works) from a table instead of a few measures.
The table should be transformed then we will have Quarters as columns and years as rows which are more dynamic, but is it easier to build the lines by measures? by using the transformed table Excel could generate the same chart while I have no idea on how to use a table to feed the Line chart.
| Q1 YTD | Q2 YTD | Q3 YTD | Q4 YTD | |
| 2022-2023 | 63 | 77 | 101 | 201 |
| 2023-2024 | 74 | 109 | 287 | 355 |
| 2024-2025 | 113 | 113 | 113 | 113 |
Thank you for reading and any comment is appreciated!
Hello MiaSunny ,
İf you'd like to use few of measure or none. You can use legend option but your table needs to be fitted for the case. For example for your case you can unpivot the column Q1,YTDQ2,YTDQ3,YTDQ4,YTD in transform data(power query). Lastly, your table needs to be like this:Year Att Val 2022-2023 Q1 63 2022-2023 YTDQ2 77 ... ... ... 2024-2025 YTD 113
In the line chart, X-axis will be year, Y-axis will be Val, Legend will be Att.Kind Regards,
Gökberk Uzuntaş
📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!
🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |
2 Replies
- uzuntasgokberk
Super User
Hello MiaSunny ,
İf you'd like to use few of measure or none. You can use legend option but your table needs to be fitted for the case. For example for your case you can unpivot the column Q1,YTDQ2,YTDQ3,YTDQ4,YTD in transform data(power query). Lastly, your table needs to be like this:Year Att Val 2022-2023 Q1 63 2022-2023 YTDQ2 77 ... ... ... 2024-2025 YTD 113
In the line chart, X-axis will be year, Y-axis will be Val, Legend will be Att.Kind Regards,
Gökberk Uzuntaş
📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!
🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok | - rohit1991
Super User
Hi MiaSunny , You can follow these steps to create the line chart.
Steps:
-
Unpivot Data in Power Query
- Select "Q1 YTD" to "Q4 YTD" → Transform > Unpivot Columns
- Rename columns:
- "Year" (Original year names)
- "Quarter" (Q1 YTD, Q2 YTD, etc.)
- "Sales" (Values)
-
Create Line Chart
- X-Axis: "Quarter"
- Y-Axis: "Sales"
- Legend: "Year"
-