Top N filter not working with a filtered table

[SC0614]

Hi,

I have a problem when I tried to filter top 20 customers in a filtered table.

The table looks like as follow:

and it has filters as follow:

Measures are:

rank_var = RANKX(ALLSELECTED(Cust_Barrie[CUST_NM]),[corr_var],,DESC)

top 20 corr_var = IF([rank_var] <= 20, [Difference],BLANK())

I want a table with only top 20 customers, but I can't filter out blank values in column "top 20 corr_var". It will return only the top 1 customer.

I think the reason is I am using "ALLSELECTED" instead of "ALL" in "rank_var". But if I use "ALL", it will rank for all customers in the dataset instead of filtered customers in the table.

Is there a way to solve this problem?

Thanks,

Sherry

[v-juanli-msft · Accepted solution]

Hi @SC0614

Use this measure for rank

Measure = RANKX(ALLSELECTED(Sheet12),[Measure 2],,DESC,Dense)

Best Regards

Maggie

[SC0614]

Hi Maggie,

Thanks for your response. It gives me every customer's list but I still can't filter out the top 20 customers. If I filter by "Measure less than or equal to 20", it would return only the first rank.

[v-juanli-msft]

Hi @SC0614

I can do it on my site, could you show me some screenshots for better troubleshooting?

Best Regards

Maggie 

[SC0614]

Thanks, Maggie. Please find my screenshot below.

Here are my measures:

• corr_var = [Standard_deviation]/[avg_dep]
  • Standard_deviation =
    STDEVX.S(
    SUMMARIZE ( 'Acct', Acct[TIME_DESC], Acct[CUST_ID] ),
    CALCULATE ( SUM ( 'Acct'[DEP_BAL_2] ) )
    )
  • avg_dep = SUM(Acct[DEP_BAL_2])/2
• Difference = [current month] - [previous month]
  • previous month = CALCULATE( SUM( 'Acct'[DEP_BAL_2] ), FILTER( Acct, 'Acct'[TIME_KEY] = MAX(Acct[TIME_KEY])-40) )
  • current month = CALCULATE( SUM( 'Acct'[DEP_BAL_2] ), FILTER( Acct, 'Acct'[TIME_KEY] = MAX(Acct[TIME_KEY])) )
• measure = RANKX(ALLSELECTED(Acct[CUST_ID]),[corr_var],,DESC,Dense)

Regards,

Sherry

[v-juanli-msft]

Hi @SC0614

Sorry, i don't find any error from your screenshot, the meaure which calculate teh rank show the correct rank from 1 to 26, not 1 for all rows.

The difference between your meaure and mine is 

• measure = RANKX(ALLSELECTED(Acct[CUST_ID]),[corr_var],,DESC,Dense)  ->yours
• measure = RANKX(ALLSELECTED(Acct),[corr_var],,DESC,Dense)                   ->mine

Best Regards

Maggie

[SC0614]

Finally worked!!! Thanks a lot!!!!