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.
Here is my data:
MainCategory- | SubCategory- | Device- | Lot- | Bin_Name- | Bin_Count |
Cat1 | SubCat1 | Dev1 | Lot1 | Bin 30 | 16 |
Cat1 | SubCat1 | Dev1 | Lot1 | Bin 31 | 22 |
Cat1 | SubCat1 | Dev1 | Lot2 | Bin 30 | 20 |
Cat1 | SubCat1 | Dev1 | Lot2 | Bin 31 | 21 |
Cat1 | SubCat1 | Dev1 | Lot3 | Bin 29 | 11 |
Cat1 | SubCat1 | Dev1 | Lot3 | Bin 30 | 17 |
Cat1 | SubCat1 | Dev2 | Lot4 | Bin 18 | 17 |
Cat1 | SubCat1 | Dev2 | Lot4 | Bin 30 | 8 |
Cat1 | SubCat1 | Dev2 | Lot5 | Bin 29 | 20 |
Cat1 | SubCat1 | Dev2 | Lot5 | Bin 30 | 21 |
Cat1 | SubCat2 | Dev3 | Lot6 | Bin 30 | 18 |
Cat1 | SubCat2 | Dev3 | Lot6 | Bin 31 | 30 |
Cat1 | SubCat2 | Dev3 | Lot7 | Bin 30 | 42 |
Cat1 | SubCat2 | Dev3 | Lot7 | Bin 31 | 22 |
Cat1 | SubCat2 | Dev4 | Lot8 | Bin 18 | 37 |
Cat1 | SubCat2 | Dev4 | Lot8 | Bin 30 | 11 |
Cat1 | SubCat2 | Dev4 | Lot9 | Bin 29 | 27 |
Cat1 | SubCat2 | Dev4 | Lot9 | Bin 30 | 28 |
Cat2 | SubCat3 | Dev5 | Lot10 | Bin 30 | 17 |
Cat2 | SubCat3 | Dev5 | Lot10 | Bin 31 | 8 |
Cat2 | SubCat3 | Dev5 | Lot11 | Bin 45 | 20 |
Cat2 | SubCat3 | Dev5 | Lot11 | Bin 85 | 21 |
Cat2 | SubCat3 | Dev5 | Lot12 | Bin 15 | 11 |
Cat2 | SubCat3 | Dev5 | Lot12 | Bin 35 | 17 |
Cat2 | SubCat3 | Dev6 | Lot13 | Bin 74 | 17 |
Cat2 | SubCat3 | Dev6 | Lot13 | Bin 89 | 8 |
Cat2 | SubCat3 | Dev6 | Lot14 | Bin 29 | 20 |
Cat2 | SubCat3 | Dev6 | Lot14 | Bin 30 | 21 |
Cat2 | SubCat4 | Dev7 | Lot11 | Bin 30 | 18 |
Cat2 | SubCat4 | Dev7 | Lot11 | Bin 31 | 55 |
I want to add following fields in Axis (MainCategory, SubCategory, Device, Lot) and show top 2 Bin_Name(s) with highest Bin_Count total.
e.g.
Resultant data should be like this:
How to achieve this?
Solved! Go to Solution.
Hi there,
Here is how I would do it, but there are definitely different ways of handling this 🙂
PBIX example here:
https://www.dropbox.com/s/t8ivi6u1iuq7856/Top%20N%20with%20multiple%20drilldown.pbix?dl=0
Sum of Bin_Count = SUM( BinData[Bin_Count] ) Sum of Bin_Count Top 2 Bin_Name per MainCategory = CALCULATE ( [Sum of Bin_Count], GENERATE ( VALUES ( BinData[MainCategory] ), TOPN ( 2, ALL ( BinData[Bin_Name] ), [Sum of Bin_Count] ) ), VALUES( BinData[Bin_Name] ) ) Sum of Bin_Count Top 2 Bin_Name per SubCategory = CALCULATE ( [Sum of Bin_Count], GENERATE ( VALUES ( BinData[SubCategory] ), TOPN ( 2, ALL ( BinData[Bin_Name] ), [Sum of Bin_Count] ) ), VALUES( BinData[Bin_Name] ) ) Sum of Bin_Count Top 2 Bin_Name per Device = CALCULATE ( [Sum of Bin_Count], GENERATE ( VALUES ( BinData[Device] ), TOPN ( 2, ALL ( BinData[Bin_Name] ), [Sum of Bin_Count] ) ), VALUES ( BinData[Bin_Name] ) )
Sum of Bin_Count Top 2 Flexible = SWITCH ( TRUE (), ISFILTERED ( BinData[MainCategory] ), [Sum of Bin_Count Top 2 Bin_Name per MainCategory], ISFILTERED ( BinData[SubCategory] ), [Sum of Bin_Count Top 2 Bin_Name per SubCategory], ISFILTERED ( BinData[Device] ), [Sum of Bin_Count Top 2 Bin_Name per Device], [Sum of Bin_Count]
)
Hi there,
Here is how I would do it, but there are definitely different ways of handling this 🙂
PBIX example here:
https://www.dropbox.com/s/t8ivi6u1iuq7856/Top%20N%20with%20multiple%20drilldown.pbix?dl=0
Sum of Bin_Count = SUM( BinData[Bin_Count] ) Sum of Bin_Count Top 2 Bin_Name per MainCategory = CALCULATE ( [Sum of Bin_Count], GENERATE ( VALUES ( BinData[MainCategory] ), TOPN ( 2, ALL ( BinData[Bin_Name] ), [Sum of Bin_Count] ) ), VALUES( BinData[Bin_Name] ) ) Sum of Bin_Count Top 2 Bin_Name per SubCategory = CALCULATE ( [Sum of Bin_Count], GENERATE ( VALUES ( BinData[SubCategory] ), TOPN ( 2, ALL ( BinData[Bin_Name] ), [Sum of Bin_Count] ) ), VALUES( BinData[Bin_Name] ) ) Sum of Bin_Count Top 2 Bin_Name per Device = CALCULATE ( [Sum of Bin_Count], GENERATE ( VALUES ( BinData[Device] ), TOPN ( 2, ALL ( BinData[Bin_Name] ), [Sum of Bin_Count] ) ), VALUES ( BinData[Bin_Name] ) )
Sum of Bin_Count Top 2 Flexible = SWITCH ( TRUE (), ISFILTERED ( BinData[MainCategory] ), [Sum of Bin_Count Top 2 Bin_Name per MainCategory], ISFILTERED ( BinData[SubCategory] ), [Sum of Bin_Count Top 2 Bin_Name per SubCategory], ISFILTERED ( BinData[Device] ), [Sum of Bin_Count Top 2 Bin_Name per Device], [Sum of Bin_Count]
)
@OwenAuger Great work, worked like a charm, Thanks.
Two more things if possible:
That's good 🙂
1. Unfortunately, from what I can tell, Power BI can't sort by two different columns in a table at the same time.
And in a clustered column chart, it doesn't seem possible to sort by value within each axis item, though I could be mistaken. Someone may have requested this(?)
2. With the measures as we have defined them, I can't see a way of eliminating space between the bars where bin names are empty.
As an alternative, we could instead define two measures: [Top Bin Sum] and [Second Bin Sum], and put them in the correct order. But then you would see the bin sums but not the correspondingbin names on the chart - probably not good enough? In a table however, you could display text measures [Top Bin Name] and [Second Bin Name].
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |