Forum Discussion

Jay_Arora's avatar
Jay_Arora
Frequent Visitor
8 years ago
Solved

Sorting by 2 columns

Hi all, 

I want to sort by table in Power BI using 2 columns. To help explain what I am trying to achieve, what I have is the blue table. I want to sort by the product table first alphabetically. Essentially, all the apples will be together and so on. Within this grouping, I want to sort by decreasing order of volume. My output should look like the green table. I tried the following link but the it does not get me to the required output.

 

https://community.powerbi.com/t5/Desktop/How-to-Rank-a-list-based-on-2-values-double-rankX/m-p/44076#M17002

 

Thanks,

Jay

 

 

  • OwenAuger's avatar
    OwenAuger
    8 years ago

    Ok Jay_Arora I would personally do something this:

    (pbix sample here)

     

    1. Create a Volume Sum measure
      Volume Sum = 
SUM ( Sales[Volume] )
    2. Create a ranking measure
      Combined Rank =
VAR ProductRank =
    RANKX (
        ALL ( Sales[Product] ),
        CALCULATE ( SELECTEDVALUE ( Sales[Product] ) ),
        ,
        ASC
    )
VAR CityRank =
    RANKX ( ALL ( Sales[City] ), [Volume Sum] )
VAR CityCount =
    CALCULATE ( DISTINCTCOUNT ( Sales[City] ), ALL ( Sales ) )
VAR CombinedRank = ProductRank
    + CityRank
    / ( CityCount + 1 )
RETURN
    CombinedRank
    3. Create a Table visual with Product, City, Volume Sum, Combined Rank, and sort by Combined Rank ascending

       

    4. Turn off Word Wrap for Column Headers and Values

    5. Narrow the Combined Rank column until it's invisible.

       

       

    Note: Creating a measure for ranking allows for the possibility that you might filter your report in the future. Otherwise, you could created a similar calculated column and sort by that instead.

     

    Regards,

    Owen

6 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    8 years ago

    Hi Jay_Arora

     

    Just clarifying, are you wanting to sort the table in the data model, or sort a table visual in your report page (which could be subject to filters on Product or City)?

     

    In either case, an approach similar to the post you linked to is possible, just the exact syntax may be a little different.

     

    Regards,

    Owen

    • Jay_Arora's avatar
      Jay_Arora
      Frequent Visitor
      8 years ago

      Thanks OwenAuger for the reply. I am looking to sort the table visual on my dashboard/report page. There are no filters on the page as I want to show all the cities and all the products.

       

      Thanks

      • OwenAuger's avatar
        OwenAuger
        Super User
        8 years ago

        Ok Jay_Arora I would personally do something this:

        (pbix sample here)

         

        1. Create a Volume Sum measure
          Volume Sum = 
SUM ( Sales[Volume] )
        2. Create a ranking measure
          Combined Rank =
VAR ProductRank =
    RANKX (
        ALL ( Sales[Product] ),
        CALCULATE ( SELECTEDVALUE ( Sales[Product] ) ),
        ,
        ASC
    )
VAR CityRank =
    RANKX ( ALL ( Sales[City] ), [Volume Sum] )
VAR CityCount =
    CALCULATE ( DISTINCTCOUNT ( Sales[City] ), ALL ( Sales ) )
VAR CombinedRank = ProductRank
    + CityRank
    / ( CityCount + 1 )
RETURN
    CombinedRank
        3. Create a Table visual with Product, City, Volume Sum, Combined Rank, and sort by Combined Rank ascending

           

        4. Turn off Word Wrap for Column Headers and Values

        5. Narrow the Combined Rank column until it's invisible.

           

           

        Note: Creating a measure for ranking allows for the possibility that you might filter your report in the future. Otherwise, you could created a similar calculated column and sort by that instead.

         

        Regards,

        Owen