Dynamic Top N Ranking with Bucket-Based Filter Context

[Julia2023]

Hi all, I need advice on implementing a dynamic Top N ranking filter in Power BI.

I have 2 visuals on the same page:

• A matrix that groups products into different sales and profitability buckets (for example, High Sales & High Profit) - Count Products measure rpovided below.
• A table that shows all individual products with an Actual Sales measure.

Above the product table, there is a Top N slicer (driven by 2&3 supporting measures) that allows users to select how many top products to display.

The issue is the following:
When I select a value in the Top N slicer (for example, Top 5) and then click a specific bucket in the matrix (e.g. High Sales & High Profit), the table still shows the overall Top 5 products, instead of the Top 5 products within the selected bucket.

What I need is for the Top N logic to be context-aware.
For example, if I select:

• Top 5 products, and
• The High Sales & High Profit bucket in the matrix,

then the table should return only the top 5 products within that selected bucket, based on the current filter context — not the global Top 5.

At the moment, the ranking ignores the bucket selection, which makes the result misleading.
I would appreciate any guidance on how to make the Top N ranking respond dynamically to the bucket selection.

Thanks in advance.  

1. Count Products = VAR SalesBucket =

    SELECTEDVALUE(Sales[Sales Name])

VAR ProfitBucket =

    SELECTEDVALUE(Profitability[Profit Name])

VAR

        CALCULATETABLE (

            ADDCOLUMNS (

                ALLSELECTED (Product[Product]),

                "@Sales", [Actual Sales $],

                "@SalesCode", [Sales Code (Products)],

                "@ProfitabilityCode", [Profitability Code]

            )

        )

VAR

    FILTER(

        SeriesTable,

        [@SalesCode] = SalesBucket &&

        [@ProfitabilityCode] = ProfitBucket

        && [@Sales] <> 0

    )

    COUNTROWS(FilterSeriesTable)

RETURN

IF(

    ISBLANK(Result),

    "",

    Result

)

2. Flag Products = VAR _top = SELECTEDVALUE('Slicer'[Parameter])

RETURN

IF([Rank Products] <= _top, 1, 0)

3. Rank Products = RANKX (ALL('Product '[Product]), [Actual Sales $])

[v-sshirivolu]

Hi @Julia2023 ,

The issue occurs because the ranking is being calculated across the entire dataset instead of within each bucket context.

To fix this, you need to explicitly preserve the bucket context while ranking.
You can achieve this by modifying the "RANKX"  measure as follows:

Rank Within Bucket =
VAR CurrentBucket = MAX(Data[Bucket])
RETURN
RANKX(
FILTER(
ALL(Data),
Data[Bucket] = CurrentBucket
),
[Total Value],
,
DESC,
DENSE
)

Then create a Top N flag measure - 

Top N Flag =
VAR N = SELECTEDVALUE('TopN Table'[N], 2)
RETURN
IF([Rank Within Bucket] <= N, 1, 0)

Finally, apply this measure as a visual level filter set to 1. This ensures Top N is evaluated within each bucket instead of globally.

Thank you.

[v-sshirivolu]

Hi @Julia2023 ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[v-sshirivolu]

Hi @Julia2023 ,

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. 

[Julia2023]

Sorry, not working... 

[v-sshirivolu · Accepted solution]

Hi @Julia2023  ,

I was able to reproduce the scenario successfully using a sample dataset, and the ranking/filtering worked correctly with the shared approach.

I have attached a sample PBIX file for reference so you can compare the implementation with your current model/setup.

If the issue still exists, please share the following details - 

sample data/model structure
expected output
current output screenshot

so the exact filter context behavior can be reproduced and checked further.

Thank you.

[Julia2023]

Thank you! It worked. The only issue I’m facing now is that in Power BI, the Rank (Top X) measure shows different values when used as a table field versus when used as a filter. The values shown in the table are correct.

Is there a workaround to create a measure for the slicer so that it returns the same values as in the table when used as a filter as well?  

Rank (Top X) = VAR _top = SELECTEDVALUE('Slicer'[Parameter]) RETURN IF( RANKX( ALLSELECTED(Product[Product]), [Actual Sales $], , DESC, DENSE ) <= _top, 1, 0 )

[v-sshirivolu]

Hi @Julia2023 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[danextian]

Hi @Julia2023 

It would be easier to provide a working solution if a sample PBIX were shared (with confidential data removed), so there would be something concrete to work with.

[Ashish_Mathur]

Hi,

Pease share the download link of the PBI file.  Show the problem and expected result there clearly.

[Juan-Power-bi]

Hi!!

The problem is your Rank Products measure uses ALL('Product'[Product]) which ignores all filters including the bucket selection. You need it to rank only within the current filter context.
Change it to use ALLSELECTED instead:
daxRank Products =
RANKX(ALLSELECTED('Product'[Product]), [Actual Sales $])
ALLSELECTED respects filters coming from slicers and cross-filtering from other visuals, so when someone clicks a bucket in the matrix it will rank only the products within that filtered set. The Top N flag then naturally applies to that ranked subset.

[Julia2023]

Thank you. I tried ALLSELECTED, but it’s still not working...