Forum Discussion

abhi201002's avatar
abhi201002
Regular Visitor
5 months ago
Solved

Unable to filter Top-N using parameters.

Let me give you some background of data and work i have done till now.   In data in have one column name Customer ID and a measure Total Sum. I want to get top-N Customers based on their Total sums...
Need Help
Show and Tell
Tips and Tricks
  • v-echaithra's avatar
    v-echaithra
    5 months ago

    Hi abhi201002 ,

    Thanks for the clarification, what you’re seeing is due to how evaluation context is affecting your measures. The main issue is the use of ALLSELECTED() in your RANKX, which makes the ranking dependent on the current filter context rather than the full customer set. As a result, the rank gets recalculated inconsistently (e.g., collapsing to only 1 customer at 100%, all 0s below that, and partial results above 100%).
    Additionally, using a measure (Dynamic Filter) that depends on rank as a visual-level filter creates a feedback loop where the filter and ranking influence each other. Your expectation (Top 5 for 5%, Top 50 for 50%) is absolutely valid, but it requires ranking over a stable, unfiltered dataset. You can partially improve this by replacing ALLSELECTED with ALL, but for consistent and scalable results, the TOPN-based approach is recommended as it avoids these context issues entirely.

    The TOPN based approach works by calculating the TopN customers in a separate virtual table, instead of relying on ranking inside the visual. First, you convert your percentage slicer into a number of customers:

    Top N Customers =
    VAR Pct = SELECTEDVALUE('%Top Customer'[%Top Customer], 1)
    VAR TotalCust = DISTINCTCOUNT(Orders[Customer ID])
    RETURN
    MAX(1, INT(Pct * TotalCust))

    Then, you build a virtual table of the top customers using TOPN:

    TopN Filter =
    VAR N = [Top N Customers]
    VAR TopTable =
    TOPN(
    N,
    ADDCOLUMNS(
    VALUES(Orders[Customer ID]),
    "@Sales", [Total Sales]
    ),[@Sales], DESC)
    RETURN
    IF(
    ISINSCOPE(Orders[Customer ID]) &&
    Orders[Customer ID] IN TopTable,
    1,0)

    Finally, apply TopN Filter = 1 as a visual-level filter.



    If further assistance is still required. 

    Please provide the sample Pbix file. How to provide sample data in the Power BI Forum

    You can refer the following link to upload the file to the community.
    How to upload PBI in Community

    We are available to support you and are committed to helping you reach a resolution.

    Best Regards,
    Chaithra E.

     

Zanqueta's avatar
Zanqueta
Super User
5 months ago

Hi , abhi201002

A more reliable and simpler approach is to create a Top‑N mechanism using TOPN() and IN, without depending on filtering the visual with a measure that also depends on the ranking.
 

Top N Customers =
VAR Pct = SELECTEDVALUE('%Top Customer'[%Top Customer], 1)
VAR TotalCust = DISTINCTCOUNT(Orders[Customer ID])
RETURN
MAX(1, INT(Pct * TotalCust))

 

TopN Filter =
VAR N = [Top N Customers]
VAR TopTable =
    TOPN(
        N,
        ADDCOLUMNS(
            VALUES(Orders[Customer ID]),
            "@Sales", [Total Sales]
        ),
        [@Sales], DESC
    )
RETURN
IF (
    ISINSCOPE(Orders[Customer ID]) &&
    Orders[Customer ID] IN TopTable,
    1,
    0
)

 
 
Apply the filter: TopN Filter = 1
No other filter based on ranking should be placed on that visual.
 

Why your original approach fails. There are two main causes:

RANKX(ALLSELECTED(...)) recalculates based on the filtered view of the visual. After applying your Dynamic Filter, ALLSELECTED sees only one customer, resulting in every row being ranked as 1.
Using a measure as both the filter and the dependency of the ranking creates a circular evaluation, which causes inconsistent results and blank visuals.

 
 
  • abhi201002's avatar
    abhi201002
    Regular Visitor
    5 months ago

    Hi Zanqueta, 

     

    First of all thanks for the alternative solution you have provided and giving your time to understand my solution.

     

    There is some clarification from my end. When i do not apply "Dynamic Filter" in filter but add the same as a column in table visualization, i get only highest sales as 1 at 100%, else at all values less than 100% i get "Dynamic Filter" as 0 and suprisingly Top 2 highest sales as 1 in "Dynamic Filter" when value is between 200-300%.

     

    I would like to clear that my desired result, for example my total customer is 100, when slicer is at 5%, is to get Top-5 customer or when it is at 50% then top-50 customer.

     

    I would surely work the solution you provided. Meanwhile it would be great if you provide whats wrong in my solution from further clarification i provided.