How to keep the x-axis static in a line & clustered column chart

[aggelina095]

Hello, 

I'm struggling with a visual in Power BI. I've created a line and clustered column chart displaying the distribution of ratings on a 5-level scale for two fiscal years (see screenshot 1). Additionally, I've added a slicer to filter the visual by department. However, in some departments with fewer employees, the data doesn't include ratings for all levels (1 to 5), resulting in the visual showing only the ratings where data is available (see screenshot 2). This creates a visually misleading representation where the distribution isn't accurately depicted. The lines are calculated as a percentage of the grand total, and the 'show no data' option doesn't seem to work.

Screenshot 1:

Screenshot 2:

Here is the dataset where the visual actual the count of employees on each grade:

Department	Performance Period	Overall Rating	Overall Rating Description
Department A	FY2023	3	Meets expectations
Department A	FY2023	3	Meets expectations
Department A	FY2023	4	Exceeds expectations
Department A	FY2023	4	Exceeds expectations
Department A	FY2023	1	Does not meet expectations
Department A	FY2023	2	Partially meets expectations
Department A	FY2023	3	Meets expectations
Department A	FY2023	2	Partially meets expectations
Department A	FY2023	3	Meets expectations
Department A	FY2023	5	Outstanding
Department A	FY2023	3	Meets expectations
Department B	FY2023	4	Exceeds expectations
Department B	FY2023	4	Exceeds expectations
Department B	FY2023	3	Meets expectations
Department B	FY2023	3	Meets expectations
Department B	FY2023	3	Meets expectations
Department B	FY2023	3	Meets expectations
Department B	FY2023	3	Meets expectations
Department B	FY2023	3	Meets expectations

Can someone please help?

[Anonymous · Accepted solution]

Hi @aggelina095 ,

Please try:

Creating a new table with a list of all of your 'Overall Rating'.

Link this to the original table, use that as the axis on the chart and make sure 'show items with no data' is ticked.

You can refer to this post.

Solved: Column Chart - Keeping X Axis Static - Microsoft Fabric Community

If you want to know more about 'show items with no data', please refer to:

Show items with no data in Power BI - Power BI | Microsoft Learn

I would be grateful if you could provide me with the data used to draw your graph, such as Count and %.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!