RankX issue getting close but still some ties that shouldn't be there

[paguy215]

I am trying to rank agents by sales based on selected date range

I have this rank formula

RANKX(ALLSELECTED('Agent List'),CALCULATE(sum(Query1[RevenueSum])),,,Skip)

Agent List is a table of unique sales IDs, Query1 is the sales data, which lists the total sales for each agent on each day

This is really close, and almost all align perfectly well, however I have few cases where the RevenueSums are different but the ranking is the same.  Also, when I change the date range the more cases like this I get ...none of these duplicate rankings are true ties

[DataNinja777]

Hi @paguy215 ,

The issue you're facing with duplicate rankings, even when the RevenueSum values are different, is likely due to floating-point precision or the lack of a unique tiebreaker. To resolve this, you can enhance your ranking formula by adding a tiebreaker that ensures uniqueness. This can be achieved by incorporating a unique identifier for each agent, such as their Agent ID, into the ranking logic. Here's the updated formula:

Rank =
RANKX(
    ALLSELECTED('Agent List'),
    CALCULATE(SUM(Query1[RevenueSum])) + DIVIDE(UNIQUEVALUE('Agent List'[Agent ID]), 1000000),
    ,
    ASC, 
    Skip
)

In this formula, the primary ranking is still based on the RevenueSum, but by adding the agent's unique ID (scaled down to a negligible value), you introduce a secondary criterion to break ties. The division ensures that the ID doesn't significantly alter the primary ranking while still differentiating agents with similar RevenueSum values. If the issue persists, you can also round the RevenueSum values in the formula to ensure consistent precision, as shown below:

Rank =
RANKX(
    ALLSELECTED('Agent List'),
    ROUND(CALCULATE(SUM(Query1[RevenueSum])), 2) + DIVIDE(UNIQUEVALUE('Agent List'[Agent ID]), 1000000),
    ,
    ASC, 
    Skip
)

This ensures that any minor inconsistencies caused by floating-point precision are eliminated. These changes should resolve your issue and ensure accurate ranking across all scenarios, including when filtering by a date range. Let me know if you encounter any further challenges.

Best regards,

[paguy215]

Thanks for the help...however, I still can't get it to work.   UNIQUEVALUE doesn't appear to be an option to use in my version.   I tried it with DISTINCT and I get an error saying "multiple values were supplied when a single value was expected"