Forum Discussion
Forced to summarise
Hello everyone,
I was working on some data viz for ASX ETFs by sectors for comparision. Got some clean data off yfinance and built a dataset as such:
On excel I am able to generate a time vs close price line chart for a sector. I was trying this out on PowerBI with all the sectors, so date on X, Sector on Legend and close price as Y, however I keep getting forced to summarise the field, even though on the table view I have it set to decimal and don't summarise. Could this be an issue with my dataset? or maybe I am not using the plot for its intended use? Any insights are welcome!
Hi Prinsh ,
The issue you're facing is likely due to the structure of your dataset. Since your dates are in columns (wide format), Power BI struggles to treat them as a proper time series. To build a time vs value plot with sectors on the legend, you’ll need to unpivot your data first.
Here’s what you can do:
- In Power Query, select all the date columns (e.g. 4/01/2023, 5/01/2023, etc.)
- Right-click and choose Unpivot Columns
- This will give you a new structure like:
ASX Code | Sector | Date | Value A200 | Financials | 4/01/2023 | 102.5 AAA | Real Estate | 4/01/2023 | 98.3 ...
- Now in your visual:
- Put Date on the X-axis
- Value on the Y-axis
- Sector in the Legend
This will allow Power BI to treat the dates as a continuous axis and plot each sector’s trend over time.
Let me know if you need help with the unpivot step or setting up the visual.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI
7 Replies
speedrampsSuper User
Use MIN., MAX or AVERAGE to summarize
You know that a sector does not have more than one ticker price for a date,
but Power BI does not know that.
It need MIN., MAX or AVERAGE to compute a scalar value to plot a line graph.
Please click thumbs up and [accept solution]
- burakkaragoz
Hi Prinsh ,
The issue you're facing is likely due to the structure of your dataset. Since your dates are in columns (wide format), Power BI struggles to treat them as a proper time series. To build a time vs value plot with sectors on the legend, you’ll need to unpivot your data first.
Here’s what you can do:
- In Power Query, select all the date columns (e.g. 4/01/2023, 5/01/2023, etc.)
- Right-click and choose Unpivot Columns
- This will give you a new structure like:
ASX Code | Sector | Date | Value A200 | Financials | 4/01/2023 | 102.5 AAA | Real Estate | 4/01/2023 | 98.3 ...
- Now in your visual:
- Put Date on the X-axis
- Value on the Y-axis
- Sector in the Legend
This will allow Power BI to treat the dates as a continuous axis and plot each sector’s trend over time.
Let me know if you need help with the unpivot step or setting up the visual.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI 
Ashish_ExcelSolution Supplier
Hi,
Even if you click on the SUM function, it should not matter because there will always be onely 1 instance of a date and sector record.
- danextian
Hi Prinsh
Whatever field placed in Y-axis has to be summarized one way or another. After all, that is meant to show the summarized data by legend or X-axis columns. Not summarizing works on tables and matrices (column/row headers) where each distinct numeric value can be in its own row.
v-hashadapuCommunity Support
Hi Prinsh ,
I wanted to follow up and see if you’ve had a chance to review the information provided here.
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions!- v-hashadapu
Hi Prinsh ,
I hope the information shared by danextian was helpful. If you have any additional questions or would like to explore the topic further, feel free to reach out. If any of the responses resolved your issue, please mark it "Accept as solution" and give it a 'Kudos' to support other members in the community.
Thank you! - v-hashadapu
Hi Prinsh , I hope you're doing well! Just checking in to see if you had a chance to review the details shared earlier. If any of the information addressed your needs, feel free to mark it as "Accept as Solution" or give it a 'Kudos' to help others in the community. Please let me know if you have any further questions!
