Forum Discussion

Julia2023's avatar
Julia2023
Helper I
3 months ago
Solved

Dynamic Top N Ranking with Bucket-Based Filter Context

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 $])

 



Need Help
Show and Tell
Tips and Tricks
  • v-sshirivolu's avatar
    v-sshirivolu
    3 months ago

    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.

11 Replies

  • Juan-Power-bi's avatar
    Juan-Power-bi
    Super User
    3 months ago

    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's avatar
      Julia2023
      Helper I
      3 months ago
      Thank you. I tried ALLSELECTED, but it’s still not working...
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 months ago

    Hi,

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

  • danextian's avatar
    danextian
    Super User
    3 months ago

    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.

  • v-sshirivolu's avatar
    v-sshirivolu
    Community Support
    3 months ago

    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's avatar
      v-sshirivolu
      Community Support
      3 months ago

      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's avatar
        v-sshirivolu
        Community Support
        3 months ago

        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.