Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
aggelina095
New Member

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

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:

 

aggelina095_0-1709142122937.png

Screenshot 2:

aggelina095_1-1709142200546.png

 

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

 

Department Performance PeriodOverall RatingOverall Rating Description
Department AFY20233Meets expectations
Department AFY20233Meets expectations
Department AFY20234Exceeds expectations 
Department AFY20234Exceeds expectations 
Department AFY20231Does not meet expectations
Department AFY20232Partially meets expectations
Department AFY20233Meets expectations
Department AFY20232Partially meets expectations
Department AFY20233Meets expectations
Department AFY20235Outstanding
Department AFY20233Meets expectations
Department BFY20234Exceeds expectations 
Department BFY20234Exceeds expectations 
Department BFY20233Meets expectations
Department BFY20233Meets expectations
Department BFY20233Meets expectations
Department BFY20233Meets expectations
Department BFY20233Meets expectations
Department BFY20233Meets expectations

 

Can someone please help?

1 ACCEPTED SOLUTION
v-huijiey-msft
Community Support
Community Support

Hi @aggelina095 ,

 

Please try:

 

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

vhuijieymsft_0-1709191967557.png

 

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

vhuijieymsft_1-1709191967558.png

 

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!

View solution in original post

1 REPLY 1
v-huijiey-msft
Community Support
Community Support

Hi @aggelina095 ,

 

Please try:

 

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

vhuijieymsft_0-1709191967557.png

 

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

vhuijieymsft_1-1709191967558.png

 

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!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors