Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Thanks,
Jay
Solved! Go to Solution.
Ok @Jay_Arora I would personally do something this:
Volume Sum = SUM ( Sales[Volume] )
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
Turn off Word Wrap for Column Headers and Values
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
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
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
Ok @Jay_Arora I would personally do something this:
Volume Sum = SUM ( Sales[Volume] )
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
Turn off Word Wrap for Column Headers and Values
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
THANK YOU!!!!!
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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |