Strict Top 10 by ABS() measure even with duplicate...

puneeth61

Power BI — Top N visual filter returning more than 10 rows due to ties (already tried rank (dense/skip created column for rank & summary table)

Problem Statement

I have a Line and Stacked Column chart in Power BI where:

X-axis: Country
Column Y-axis: Measure = Sleeping Difference = Sleeping SFDC This Week – Sleeping SFDC Last Week
Line Y-axis: Another measure (not relevant to this issue)

I want to filter to Top 10 countries by absolute Sleeping Difference.

DAX Definitions

Sleeping Difference =
[Sleeping SFDC This Week] - [Sleeping SFDC Last Week]

ABS Sleeping Difference =
ABS([Sleeping Difference])

Sleeping SFDC This Week

Sleeping SFDC This Week =
CALCULATE(
    COUNT('Sleeping IB+Lost'[Equipment #]),
    'Sleeping IB+Lost'[Week] = MAX('Sleeping IB+Lost'[Week])
)

Sleeping SFDC Last Week

Sleeping SFDC Last Week =
VAR RankedWeeks =
    ADDCOLUMNS(
        VALUES('Sleeping IB+Lost'[Week]),
        "WeekRank", RANKX(VALUES('Sleeping IB+Lost'[Week]), 'Sleeping IB+Lost'[Week], , DESC)
    )
VAR SecondMaxWeek =
    MAXX(FILTER(RankedWeeks, [WeekRank] = 2), 'Sleeping IB+Lost'[Week])
RETURN
    CALCULATE(
        COUNT('Sleeping IB+Lost'[Equipment #]),
        'Sleeping IB+Lost'[Week] = SecondMaxWeek
    )

The Issue

When I use the Top N filter in the Visual Filter pane:

Top N = 10 by [ABS Sleeping Difference]
If multiple countries have the same value (ties), Power BI includes all tied countries, so I get more than 10 countries in the visual — sometimes 17, 20, or even 30.

What I Have Already Tried (to avoid duplicate answers)

Top N visual filter — Standard approach (Top N by ABS Sleeping Difference)
- ✅ Works for most cases, ❌ still returns >10 when there are ties.

Summary table with TOPN()

Top10Countries_SleepingDiff =
TOPN(
    10,
    SUMMARIZE(
        'YourTable',
        'YourTable'[Country],
        "AbsDiff", ABS([Sleeping Difference])
    ),
    [AbsDiff],
    DESC
)

Used TREATAS and visual filters to show only countries in this table.
❌ Still shows >10 due to ties.

RANKX approach
```
CountryRank =
RANKX(
    ALL('YourTable'[Country]),
    ABS([Sleeping Difference]),
    ,
    DESC,
    Dense
)
```
- Tried both Dense and Skip ranking methods.
- Applied FILTER(CountryRank <= 10) in visuals.
- ❌ Still includes extra countries when ties occur at rank 10.
Manual visual filter using a rank column
- Created a calculated column with rank.
- Set visual filter to Rank <= 10.
- ❌ Did not solve the problem when multiple rows per country exist or when ties occur at the cut-off.

What I Need Help With

I’m looking for a robust approach to:

Show exactly 10 countries in the visual.
Break ties deterministically (for example, by adding a secondary sort like Country Name).
Avoid including all tied countries if they push the total above 10.

Sample Data (for testing)

Country Week Sleeping SFDC This Week Sleeping SFDC Last Week

USA	32	12	8
Canada	32	20	16
Mexico	32	5	8
UK	32	9	2
Germany	32	15	20
France	32	11	15
Italy	32	3	1
Spain	32	25	21
Japan	32	14	10
China	32	18	19
India	32	4	0
Brazil	32	17	15
Australia	32	8	8
Argentina	32	6	3
South Africa	32	7	1
Russia	32	5	2
Netherlands	32	9	4

Request

If you know a DAX or modelling technique that will:

Always return exactly 10 countries,
Handle both positive and negative differences using ABS,
Break ties consistently without exceeding 10,

…please share your approach.

johnt75

Create a measure like

Rank =
RANK (
    ALL ( Countries[Country] ),
    ORDERBY ( [Abs Sleeping Difference], DESC, Countries[Country], ASC )
)

In the event of ties in the sleeping difference it will rank by country name in alphabetical order. Add this measure as the top n filter on the visual.