Dynamic filtering TopN calculated table in Power BI desktop

[kcheyney]

Hi I have a table that has a Category and a Group and some numeric measure (called count for this example).  I want to be able to create a filter on category and use it to filter a calculated table that returns the Top N for the count field.  Here is my example table:

Category	Group	Count
A	r	70
A	w	24
A	z	37
A	u	16
A	y	83
A	q	32
A	t	81
A	x	45
A	v	36
A	s	94
B	m	36
B	o	45
B	l	77
B	q	28
B	n	16
B	p	29
B	r	86
B	j	10
B	k	19
B	s	57
C	c	46
C	i	51
C	d	80
C	b	83
C	j	77
C	h	34
C	a	65
C	g	59
C	e	65
C	f	42

I want to create Filter on my report for Category and then have it cross filter a table or chart on the report that shows the Top N (in this example 5) Groups by Count. as shown in the two pictures below (filtered on A and then on C)  I am very new to DAX and haven't figured out how to do this.  Any help would be greatly appreciated!

Top 5 filtered on category ATop 5 filtered on category C

[seena]

Dynamic filtering TopN calculated table in Power BI desktop

[seena]

me too. I have a similar request from my customer. Struggling to get the numbers correct
That will work if you put a visual filter to select the TOPN however, it does not work if you want the TOPN to be dynamic

[Anonymous]

Have you figured out how to overcome this problem, seena? I have a similar problem. 

[seena]

How to get top 10 customers in a Dashboard

To select TOPN customers .

use below method

Step1: Group the customers and put them in a new table -GroupBycompany

table=groupby('product','product'[company_name],"count",sumx(currentgroup(),product[invoice total])

[update the count column as DO NOT Summarize]

Step2: Create a rank measure in the original 'Product' table

Step3: rank=rankx(all('table'),'product'[total_invoice]

where (another measure in table product )   Total_invoice = sum('product'[invoice total]

Create a chart and select

Company name from 'Table'

Invoice Total from 'Product'

Put measure' rank' as filter and value = less than 11 (for Top 10 customers)

If you want to dynamically select TOP10 TOP20 or TOP30 customer then

Create a new table TOPN with column name TOPN and values (10,20,30)

Add a new column to it SelectedTopNNumber=MIN('TOPN'[TopN])

add another measure to original 'Product' table -- Check = if([rank] <= [SelectedTopNNumber] ,1,0)

Add the column TOPN as a slicer

Create a pie chart with the company name from the new table’ GroupBycompany’ and invoice from the original table Product

Add measure 'check' as a filter with value =1

Revenue from TOPN Customers = CALCULATE(sum('product'[Invoice Total]),filter('Table',[rank] <= 'TOPN'[SelectedTopNNumber]))

Name changed as required :

SelectedTopnNNumber = min('TOPN'[TopN])

Slicer = if([Rank]<=[SelectedTopnNNumber],1,0)

If ‘All’ to be included in Filter criteria:

Enter TopN as Text and read it as text in the measure

Slicer = if([SelectedTopNNumber]= "5",if([Rank]<=5,1,0),(if([SelectedTopNNumber]="10",if([Rank]<=10,1,0),if([SelectedTopNNumber]="20",if([Rank]<=20,1,0),if([SelectedTopNNumber]="All",if([rank]>=1,1,0),1)))))

Revenue From TopN Customers = CALCULATE(sum(invhdr[Invoice Total]),FILTER(GroupByCompany,if('TOPN'[SelectedTopnNNumber]="5",[Rank]<=5,if('TOPN'[SelectedTopnNNumber]="10",[rank]<=10,if('TOPN'[SelectedTopnNNumber]="20",[rank]<=20,if('TOPN'[SelectedTopnNNumber]="All",[rank]>=1,[Rank]<=1))))))

Enjoy dynamic selection of TOPN customers

[Anonymous]

Thank you for sharing, seena

[EdEvetts]

Hi kcheyney, did you work out how to do this? I am having a similar issue myself

thanks Ed

[kcheyney]

Nope, not yet