Forum Discussion

kcheyney's avatar
kcheyney
New Member
10 years ago

Dynamic filtering TopN calculated table in Power BI desktop

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 t...
Need Help
seena's avatar
seena
Frequent Visitor
9 years ago

Dynamic filtering TopN calculated table in Power BI desktop

seena's avatar
seena
Frequent Visitor
9 years ago

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's avatar
    Anonymous
    Not applicable
    9 years ago

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

     

    • seena's avatar
      seena
      Frequent Visitor
      9 years ago

      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's avatar
        Anonymous
        Not applicable
        9 years ago

        Thank you for sharing, seena