Sort a Dimension Table with a measure where some rows in the Dimension Table will have a blank value

[AGajreeFM]

Hi

I want to sort a Dimension Table by a measure, lets say [Total Sales], but some rows in the dimension table have no sales so this meant an attempt with RANKX didn't work as that provided ties and meant I could not sure the Sort By Column as values must be unique

Can anyone advise or point to a blog or article?

Thanks in Advance

[v-mengmli-msft]

Hi @AGajreeFM ,

According to your description, you want the values in the slicer to be sorted by the corresponding sales amount. If I understand you correctly, you can consider creating Sorting table. Here is my test for your reference.

1. Calculate the sales corresponding to the product.

2. Sort sales column.

3. Creating the Sorting Table and have the product column sorted by the rank column.

Then use the product column of the Sorting Table in the slicer and select the sort order. Alternatively, you may need to create a relationship between the Sorting Table and the Fact Table to enable filtering.

If the above is not what you want, please provide data and examples to explain the results you want.

——————————————————————————————————————————————————

If my answer helps you solve the problem, please accept my answer as a solution and let it be seen by more people in need.

Best regards,

Mengmeng Li

[amitchandak]

@AGajreeFM , You have sort option on visual to sort based on measure. Recently its has been added for Legends too under properties.

Only for Matrix visual column, you do no have option sort based on measures.

Sort by values for legend

[AGajreeFM]

Hi

I wish to sort the slicer in descending order of sales, so not the visual

[rohit1991]

Hi @AGajreeFM ,

To sort a Dimension Table by a measure (e.g., Total Sales) in Power BI while handling blank values and ensuring unique rankings, you can modify your RANKX approach to avoid ties and blank values. The issue arises because RANKX assigns the same rank to multiple rows with the same measure value, and Power BI’s Sort By Column requires uniqueness.

You can create a calculated column using RANKX while incorporating a secondary sort condition (like an ID column) to break ties:

RankedSales = 
VAR SalesRank = 
    RANKX(ALL('DimensionTable'), [Total Sales], , DESC, DENSE)
VAR UniqueRank = 
    SalesRank + DIVIDE(DISTINCTCOUNT('DimensionTable'[ID]), 1000000)
RETURN 
    IF([Total Sales] = BLANK(), BLANK(), UniqueRank)

This method ensures unique ranking, correct sorting, and proper handling of blank values, allowing your Dimension Table to be sorted accurately by Total Sales without violating Power BI’s Sort By Column constraints.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.
Connect with me on LinkedIn: Rohit Kumar

[AGajreeFM]

Thannk you for your reply but I need a unique rank for the blank values too otherwise I cannot use the SortByColumn on the dimension table with the new calculated column as that needs unique values?