Forum Discussion
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 in my visualization.
I created a parameter range %Top Customer. It have a measure inside it which is %Top Customer Value =
Please point out the mistake and give the correct method to achieve the same.
I hope i am able to explain problem and solution i did.
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 CommunityWe are available to support you and are committed to helping you reach a resolution.
Best Regards,
Chaithra E.
5 Replies
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])RETURNMAX(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)RETURNIF (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.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.
Hi,
Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
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 CommunityWe are available to support you and are committed to helping you reach a resolution.
Best Regards,
Chaithra E.Hi abhi201002 ,
Just checking in to see if you had a chance to follow up on our earlier conversation. If you're still encountering the issue, please share the sample data so we can assist you with an accurate solution.
If you have any further questions, feel free to reach out anytime.
Thank you.
