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

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.

OwenAuger's avatar
OwenAuger
Super User
8 years ago

Hi Jay_Arora

 

I don't think there should be a problem with the CityRank determined within the formula, but I could be missing something.

 

The CityRank variable determines the rank of  [Volume Sum] in the current filter context, compared against [Volume Sum] evaluated in the context of every possible City with other filters left unchanged.

 

In the context of the table visual, each row has a Product filter and a City filter.

So CityRank in a given row of the table will be the rank of the current row's City (& Product) compared with all possible Cities (& the same Product).

 

If there are Cities that don't exist with the current Product, they will product a (blank) value for [Volume Sum] when RANKX iterates over them, but that shouldn't affect the relative ranking of Cities that do exist with the current Product.

 

But I could have missed something. Could you provide an example of data where the formula breaks?