Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Column Chart - Keeping X Axis Static

I'm trying to create a clustered column chart where the x axis stays static regardless of slicers and values but can't seem to get it to work. I've tried the following two suggestions that have poppe...
Need Help
  • AntonioM's avatar
    AntonioM
    4 years ago

    Hello,

     

    The only way I've managed it is by creating a new table with a list of all of your categories. 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'd then need to change your measures to:

    Individual = CALCULATE(SUM(Sheet1[Count]),ALLEXCEPT(Sheet1,Categories[Categories],Sheet1[User]))
Group = CALCULATE(SUM(Sheet1[Count]),ALLEXCEPT(Sheet1,Categories[Categories]))

     

    Hope that's alright!

AntonioM's avatar
AntonioM
Solution Sage
4 years ago

Hello,

 

The only way I've managed it is by creating a new table with a list of all of your categories. 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'd then need to change your measures to:

Individual = CALCULATE(SUM(Sheet1[Count]),ALLEXCEPT(Sheet1,Categories[Categories],Sheet1[User]))
Group = CALCULATE(SUM(Sheet1[Count]),ALLEXCEPT(Sheet1,Categories[Categories]))

 

Hope that's alright!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thank you, that seems to have done the trick.

 

Also, just in case anyone else may find the following helpful: I originally ran into some issues with this solution because in my actual dataset, Category contains nulls so PBI was forcing a many-to-many relationship, which caused issues. Once I replaced the nulls with a different value everything seemed to work.