Sorting by 2 columns

[Jay_Arora]

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

Thanks,

Jay

[OwenAuger]

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]

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 · Accepted solution]

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]

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

[Jay_Arora]

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]

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?