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

puneeth61 — Sun, 10 Aug 2025 03:40:24 GMT

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.

Re: Strict Top 10 by ABS() measure even with duplicate values

johnt75 — Mon, 11 Aug 2025 15:07:04 GMT

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.

Re: Strict Top 10 by ABS() measure even with duplicate values

Anonymous — Thu, 14 Aug 2025 09:22:09 GMT

Hi puneeth61 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

Re: Strict Top 10 by ABS() measure even with duplicate values

Anonymous — Mon, 18 Aug 2025 15:43:08 GMT

Hi puneeth61

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

Re: Strict Top 10 by ABS() measure even with duplicate values

Anonymous — Sat, 23 Aug 2025 13:18:35 GMT

Hi puneeth61

As we haven’t heard back from you, we wanted to kindly follow up to check if the suggestions provided by the community members for the issue worked. Please feel free to contact us if you have any further questions.

Thanks and regards

topic Re: Strict Top 10 by ABS() measure even with duplicate values in DAX Commands and Tips

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

Problem Statement

DAX Definitions

The Issue

What I Have Already Tried (to avoid duplicate answers)

What I Need Help With

Sample Data (for testing)

Request

Re: Strict Top 10 by ABS() measure even with duplicate values

Re: Strict Top 10 by ABS() measure even with duplicate values

Re: Strict Top 10 by ABS() measure even with duplicate values

Re: Strict Top 10 by ABS() measure even with duplicate values