The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Dynamic filtering TopN calculated table in Power BI desktop
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
Have you figured out how to overcome this problem, seena? I have a similar problem.
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
Thank you for sharing, seena
Hi kcheyney, did you work out how to do this? I am having a similar issue myself
thanks Ed
Nope, not yet
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
73 | |
36 | |
30 | |
23 | |
14 |