Using a Categorical Variable Parameter on Axis and Sort Reverting to Default.

Question

I have a dashboard where I want to show two graphs side by side--one that is filtered by a variable and one that will always show the overall results for the same value. I was able to get this to work, however one of the axes of the graphs is a categorical variable parameter and every time I switch the parameter it switches back to default sorting behavior, which is by categorical variable.

The effect of this is that that sometimes the categories I want to compare are not in the same order across both graphs, which disrupts their use as a comparison tool.

Here's an example of the problem based on the sample data:

The month name filter only applies to the right graph while the parameter changes how profit is disaggregated between both.

I am able to use the "Sort Axis" option to sort by whatever variable is currently selected in the parameter, but as soon as the parameter changes it defaults back to sorting by the numeric variable and remains that way even when switching back to the parameter where the change was made.

Thank you for any help getting this to sort correctly.

parry2k · Answer

hjolley&nbsp;maybe you want to use matrix visual for this, add two measures, one for left and one for right, put both measures in the values section, while country on the rows, and then use data bar in conditional formatting. This way you will not run into this issue and the comparison will be super easy.

hjolley · Answer

Unfortunately, I prefer the look of the column charts and the real measure is a complicated % of total that already makes use of ALLEXCEPT and I haven't been able to get one that doesn't react to the filter in question that also calculates correctly.&nbsp;

anonymous · Answer

Thanks for the reply from parry2k&nbsp;, please allow me to provide another insight:Hi,&nbsp;hjolley&nbsp;
Your approach to sorting the columns is correct, but you need to sort each column selected by your parameter. For example:
Here is my sample data:

1.I have sorted each column based on the index column:

2.Here is the final result, which I hope meets your requirements:

&nbsp;
Please note that the columns being sorted must not contain duplicate values, as this will result in a sorting error.
&nbsp;
Please find the attached pbix relevant to the case.
&nbsp;
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;
&nbsp;

hjolley · Answer

All the categorical columns have duplicate values. I am plotting a metric against a changing categorical variable so all the values are a creation of many, many rows.&nbsp;I'm not entirely sure why your PowerBI graphs don't have a default sort axis but the problem is that whatever I do it defaults back to the numeric metric like so:&nbsp;&nbsp;&nbsp;Vs yours, which does not in fact have a default sort at all:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi,&nbsp;hjolley&nbsp;
Thank you for your swift response.
&nbsp;
I apologise for any confusion caused. Your sorting method differs from mine. I use the position shown in the image below:

If your sorting column contains duplicates, the corresponding index values for those rows must also be duplicated:

&nbsp;
I recommend using my method for sorting, as it can more effectively resolve your issue.Of course, if you have any new ideas, you are welcome to contact us.&nbsp;
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.