Forum Discussion
Combo Chart with Line Issue
Hi there, I am at my wit's end. Is there some way to get the line to chart out a marker for the Company Goal for each Team and Year as what is shown in the table? The table is correct. The column data is correct. And the years is what it is. The problem is the Line Chart is showing the Average Total across for all Years and all teams - 16%. The data that is shown for each Team and Year is correct, but for the life of me I cannot get it to plot out 20 markers (4 teams X 5 years). Thank you in advance!
5 Replies
Hi DPCCGF ,
Based on the screenshot you provided, I created the following example data and created the calculated columns.
Would you expect results similar to those shown in the chart below?
Please let me know if I've misunderstood anything.
If the the reply still not help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
How to provide sample data in the Power BI Forum
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best regards,
Lucy Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This looks great, but is there an yway I can get the Year to be the legend instead of in the X- Axis?
Team Year Company Goal (Team) Team Percent Blue 2020 17% 16% Blue 2021 17% 19% Blue 2022 16% 11% Blue 2023 14% 13% Blue 2024 14% 10% Gold 2020 17% 19% Gold 2021 17% 15% Gold 2022 16% 12% Gold 2023 14% 10% Gold 2024 14% 11% Red 2020 17% 18% Red 2021 17% 17% Red 2022 16% 16% Red 2023 14% 19% Red 2024 14% 10% Sage 2020 17% 19% Sage 2021 17% 16% Sage 2022 16% 11% Sage 2023 14% 13% Sage 2024 14% 8% Hi DPCCGF ,
Thank you for your kind reply!
When you add Year to the legend of a line and cluster bar chart instead of the x-axis, the line chart shows the average total for all years and all teams (16%). This is because the chart treats “Year” as a categorical variable rather than a numeric variable. Therefore, it aggregates the data for all years and calculates the average.
You could try the methods below to solve the problem.
1. Add “Year” to the X-axis: Make sure that “Year” is added to the X-axis and not to the legend. This way, the chart will treat the year as a numeric variable and display the data points for each year on the X-axis.
You could set your own colors to better differentiate between Years.
2. Add “Team” to Legend: Add “Team” to the legend so that each team's data will have a different color or style for easy differentiation.
Please let me know if I've misunderstood anything.
Best regards,
Lucy Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Lucy, these look great. I need to crunch these down a bit width wise. I have to live with a 375 width as they place these into an annual report. So I had something like the below but each of those lines is a single chart overlayed on the bar chart. Is there a way in your charts to leave them as is but hide that year so I can reduce the width. Then I could make a year legend and work the colors that way?
