Forum Discussion

viswaaa's avatar
viswaaa
Helper IV
9 months ago
Solved

Nested sorting in Matrix

Hi All,

 

I have a country column and created a bucket to sho others as separate.so created below dax.

Placed country_group and country in rows in matrix

 

Country_Group =
IF(
'Table'[Country] IN { "Other 1", "Other 2" },
"Others",
'Table'[Country]
)

 

My requirement is I need to show others always bottom though they have high profits and sort inside counties

Attached is my data and output also.

 

Please help how to get this view

 




Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    9 months ago

    Hi,

    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    It seems like sorting by sorting by two columns in the matrix visualization shows expected result.

    Please check the below picture and the attached pbix file.

     

     

     

5 Replies

  • danextian's avatar
    danextian
    Super User
    9 months ago

    Hi viswaaa 

     

    Create a custom column and use it to sort country group - Sort one column by another column in Power BI 

    Country_Group  Sort =
IF ( 'Table'[Country] IN { "Other 1", "Other 2" }, 1, 0 )

     You will also need to create another sort column for country but you need to do it in Power Query as referencing the column being sorted by in the sort column will return  a cyclical error. 

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    9 months ago

    Hi,

    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    It seems like sorting by sorting by two columns in the matrix visualization shows expected result.

    Please check the below picture and the attached pbix file.

     

     

     

    • viswaaa's avatar
      viswaaa
      Helper IV
      9 months ago

      Hi Jihwan,

       

      I am not able to do multisort.Please help how to do this.

      When I click on ohter sort is replacing

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        9 months ago

        Hi,
        Hmm... I think it is not a good way to do multi sorting in matrix visualization, but please check the steps below that I tried to do.

        - on the report canvas,

        - create new table visualization (not matrix visualization)

        - insert columns ( Country group, Country, Sum of sales, Sum of profit)

        - click Country group column name on the visual to sort

        - And then, SHIFT + click Sum of profit column name on the visual to multi sort.

        - And then, change the visual to Matrix visualization

        - If one of columns are in Columns area, move it to Rows area. And then drill down to see all information.

        Sorry that I could not create a short recording file to record my screen, but I hope the above helps.

        Thank you.