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

[hjolley]

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]

@hjolley 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]

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. 

[Anonymous]

Thanks for the reply from parry2k , please allow me to provide another insight:
Hi, @hjolley 

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:

Please note that the columns being sorted must not contain duplicate values, as this will result in a sorting error.

Please find the attached pbix relevant to the case.

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.

[hjolley]

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.

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:

Vs yours, which does not in fact have a default sort at all:

[Anonymous]

Hi, @hjolley 

Thank you for your swift response.

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:

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.
 

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.

[hjolley]

I have no concerns with the sort order of the categorical variable when I can get the graph to sort by the categorical variable. The problem is that my graphs for some reason will not stay sorted by the character variable.

I don't know why your graphs don't have a default sort axis (maybe because the data is already summarized?) but I believe that is the core of the problem, not what the categorical variables are sorted by (currently, they are sorted by themselves).

I don't care if the data remains in data source order; I care that two graphs sort the same way, whatever that is.

[Anonymous]

Hi, @hjolley 

Thank you for your swift response.

Your observation is very detailed. However, I need to clarify further:

In my visualisation, the reason for not using the default sorting and instead sorting by the index column is that I modified each column’s “sort by column” setting and sorted by the index column.

This is why I recommend using the “sort by column” method to sort the x-axis of your visualisation.

I hope my explanation is clear enough. If you have any further questions, please feel free to contact us.

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.