Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
20 | |
19 | |
18 | |
9 |