Forum Discussion

hjolley's avatar
hjolley
Regular Visitor
1 year ago

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

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 wor...
Need Help
parry2k's avatar
parry2k
Super User
1 year ago

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's avatar
hjolley
Regular Visitor
1 year ago

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's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      hjolley
      Regular Visitor
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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.