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 alphabet...
  • 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

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

      • Jay_Arora's avatar
        Jay_Arora
        Frequent Visitor
        8 years ago

        Thanks Owen. This works. I do have a follow on question.

         

        Why are we using city column in the combined rank? There might be case where the cities are different for each product or each product might be sold in different number of cities. The formula breaks in that case.