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

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

jlinn's avatar
jlinn
Frequent Visitor
6 years ago

THANK YOU!!!!!

 

 

Combined Sort =
VAR MONTHRANK = RANKX(ALL('Date'[YearMonth##]),Calculate(Selectedvalue('Date'[YearMonth##])),,ASC,Dense)
VAR ABRANK = RANKX(ALL(OPPORTUNITY[Opportunity Name (Today)]),[AB Weighted (Current)],,DESC,SKIP)
VAR OPPCOUNT= CALCULATE ( DISTINCTCOUNT ( Opportunity[Opportunity Name (Today)] ), ALL (OPPORTUNITY) )
VAR Combined=MONTHRANK+ABRANK / (OPPCOUNT +1)

Return

Combined