Forum Discussion
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 popped up in searches but neither works:
1) Changed the X axis type to "Categorical"
2) Added a "+0" to the end of the measures
Here's how it looks with no slicers selected:
Here's how it looks with User B selected. Category 3 disappears for both the Individual and Group, I'd like Individual to display 0 and Group its full value:
A sample dataset is below, along with the measure calculations. Not sure what I'm missing, would appreciate any help.
Individual = CALCULATE(SUM(Sheet1[Count]),ALLEXCEPT(Sheet1,Sheet1[Category],Sheet1[User]))+0
Group = CALCULATE(SUM(Sheet1[Count]),ALLEXCEPT(Sheet1,Sheet1[Category]))+0
| User | Category | Count |
| A | 1 | 1 |
| A | 1 | 1 |
| A | 2 | 1 |
| A | 3 | 1 |
| A | 4 | 1 |
| A | 4 | 1 |
| B | 4 | 1 |
| B | 4 | 1 |
| B | 1 | 1 |
| B | 1 | 1 |
| B | 2 | 1 |
| B | 2 | 1 |
| B | 2 | 1 |
| B | 4 | 1 |
| C | 1 | 1 |
| C | 2 | 1 |
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!
2 Replies
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!
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.