Aplying TOP N filter and breaking ties

[duddys]

Iam encountering a problem, where I have ties using TOP N filter and I need to show only N, not N + how man ties are. The problem is, there is no other column in table I can break the tie. Tried to use chatgpt to help but nothing work. To be more exact, I have table with errors and amount of occurance of the error and I want to show the most 5 frequent ones. Chatgpt suggested to make ranking measure and then use filter to show only rank 5 and lower, but nothing worked so I am trying my luck here.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result in a simple table format.  Share data in a format that can be pasted in an MS Excel file.

[v-linhuizh-msft]

Thanks for th replies from rajendraongole1.

Hi @duddys ,

If you don't want to show a tie, you can create an index column and sort by the index column if the values are the same.

newrank = RANKX(
    ALL('Table'),
    CALCULATE(
        SUM('Table'[Occurrence])*1000 + SUM('Table'[Error ID])
    ),
    ,
    DESC,
    Dense
)

Result:

Or create a new calculated column and a measure:

combine = 'Table'[Occurrence]&'Table'[Error ID]

rank = RANKX(ALL('Table'),CALCULATE(SUM('Table'[combine])),,DESC,Dense)

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[rajendraongole1]

Hi @duddys - First, create a measure that ranks each error by its occurrence. Power BI's RANKX function allows you to rank values without adding a calculated column to your data, which keeps your data model more efficient.

create below measure:

Error Rank =
RANKX(
ALL('ErrorsTable'), // Replace 'ErrorsTable' with your actual table name
[Occurrence], // The column or measure you're ranking (e.g., the error count)
, // Skip this argument if ranking in descending order
DESC, // Sort by descending order to get highest occurrences ranked first
DENSE // Use "DENSE" to avoid gaps in ranking
)

Now, create another measure that will return the occurrences only for the top 5 errors based on the rank.

Top 5 Errors Occurrence =
IF(
[Error Rank] <= 5, // This condition checks if the rank is in the top 5
[Occurrence], // Return the occurrence count if it is
BLANK() // Otherwise, return BLANK() so it's hidden in visuals
)

In your table visual, add the [Error Rank] and [Top 5 Errors Occurrence] measures.
Set a filter on the visual to display only rows where [Top 5 Errors Occurrence] is not blank. This will automatically restrict the table to the top 5 errors without including additional rows due to ties.Hope this works 

[duddys]

So I modified it for my use

Amount Rank =

RANKX(

    ALL('Query2'),           // Remove any filters on the Query2 table

    SUM('Query2'[amount]),   // Sum the 'amount' column for each row context (aggregation)

    ,                         // No tie-breaker (default behavior)

    DESC,                     // Rank in descending order (higher amounts get a better rank)

    DENSE                     // Use DENSE ranking to avoid gaps in ranks

)
.But when i add the amount rank to visual, just for clarification it works, all is ranked 1.

[rajendraongole1]

Hi @duddys -It sounds like the Amount Rank measure is ranking all rows as 1 because the calculation context isn't set up as expected. When you use ALL('Query2') in the RANKX function, it removes all filters from Query2, making it calculate ranks across the entire dataset without considering any row-level context or groupings.

if you want to rank within group 

Amount Rank =
RANKX(
ALL('Query2'[Team]), // Keep context for Team to rank within each Team group
CALCULATE(SUM('Query2'[amount])), // Calculate sum in the context of the visual filters
, // No tie-breaker column
DESC, // Rank in descending order
DENSE // Dense ranking to avoid gaps
)

replace team with your valid column 

another approach is you also try with remove filters

Amount Rank =
RANKX(
REMOVEFILTERS('Query2'[amount]), // Remove only specific filters if necessary
SUM('Query2'[amount]),
,
DESC,
DENSE
)

try it and let me know , if possible please share pbix file by removing the sensitive data.

[duddys]

Okay i made it rank by the name of error, but it has multiple of the same rank and I need the tie breaks am I right ? how do i achieve it

[duddys · Accepted solution]

So, if somebody is interested, here is the solution I came up with. After a long discussion with chat gpt, we came up with this. I dont know how exactly it works, but it works. Based on the earlier function it just ranks the rows incrementing by 1, even if I have for example 4 tie breaks followed by 5 tie breaks, it just ranks from 1...9. So, here is what worked for me. Then I just add TopN filter based on Rank column.