TOP N After a Table Filter Is Applied

[bendsteel6]

Hi,
I have a situation where I want to get the TOP N in this table (by Avg_Claim_Pay) but only after the filter on Claims (where the count of Claims is > 10) is applied. Right now if I put a TOP N on the PART_NAME based on Avg_Claim_Pay, it will show only one or two records, because many of the one with the highest Avg_Claim_Pay only have one ot two claims. I've done a lot of searching on TOP N but every post I've come across doesn't address calculated or summarized fields. How can I show the TOP N on this table after the 'Claims > 10' filter is applied?

Thanks in advance!

[v-echaithra · Accepted solution]

Hi @bendsteel6 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra E

[bendsteel6]

Thank you for this.  It worked wonderfully!

[bendsteel6]

This worked wonderfully!  I had to do the RANK by PART_NAME and DEALER, but it worked great!  Thanks!

[v-echaithra]

Hi @bendsteel6 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @bendsteel6 ,

Thank you for reaching out Microsoft Community.

You can apply a TOP N filter on a field in a table visual, after a filter has been applied on another field, such as filtering PART_NAMEs where Claims > 10. The TOP N filter (in the visual’s filter pane) doesn't "see" filters applied by other visuals or measure logic, so it evaluates TOP N before filters like Claims > 10 are applied — hence the unexpected behavior you're seeing.

Power BI’s built-in TOP N filter in the visual pane does not respect filters applied by measures or conditions like "Claims > 10". Still here is an workaround you can try.

Let’s say your table visual includes:

Psrt_name
Avg_Claim_Pay (a measure)
Claims (a count of claims — also a measure),

and you want:

Only rows where Claims > 10
Then, among those, the Top N by Avg_Claim_Pay

Create a Claim Count measure:

Claim_Count =
CALCULATE(COUNTROWS('ClaimsTable'))

Create Avg_Claim_Pay if you haven't already:

Avg_Claim_Pay :=
DIVIDE(SUM('ClaimsTable'[Claim_Pay]), [Claim_Count])

Create a ranking measure that respects the Claims > 10 condition:

Rank_By_Avg_Claim_Pay :=
RANKX(
FILTER(
ALL('ClaimsTable'[PART_NAME]),
[Claim_Count] > 10
),
[Avg_Claim_Pay],
, DESC )

Apply visual-level filters:
Claim_Count > 10

Rank_By_Avg_Claim_Pay <= N

If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

Regards,
Chaithra

[v-echaithra]

Hi @bendsteel6 ,

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Chaithra.

[bendsteel6]

Hi Chaithra,

No, it did not answer my question.  I thought I explained quite well, where I simply wanted to know if there's a way to use a TOP N on a field in a table where one of the other fields in the table (visual) has a filter on it.  It was responded to with what seemed to be a canned response, wanting to see more info and what the desired outcome is.  In a power BI this size (very large) with sensitive data, trying to build what was asked for would take very long, so if I can free up some time to do that in the near future, I will.  But until then, this goes unanswered.  Sorry.

Thanks!

[Ritaf1983]

Hi @bendsteel6 
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523