Dynamic filtering TopN calculated table in Power BI desktop

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