Combo Chart with Line Issue

[DPCCGF]

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! 

[DPCCGF]

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%

[Anonymous]

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.

[DPCCGF]

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?

[Anonymous]

Hi @DPCCGF ,

Could you please tell me in more detail what you are trying to achieve, I don't think there is anything particularly wrong with the screenshots you have provided, and what do you expect the final result to look like?

Thank you for your understanding!

Best regards,

Lucy Chen

[Anonymous]

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.