Top N with multiple drill down

[javedbh]

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.

1. when I am on level “MainCategory” then chart should show top 2 bin names for Cat1 and Cat2 separately.
2. When I am on level SubCategory then chart should show top 2 bin names against SubCat1, SubCat2, SubCat3, SubCat4.
3. When I am on level Device then chart should show top 2 bin names against Dev1, Dev2, Dev3, Dev4, Dev5, Dev6, Dev7
4. and so on.

Resultant data should be like this:

How to achieve this?

[OwenAuger · Accepted 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

1. I would create three measures that give you the bin count for top 2 bins at the three different levels. GENERATE/TOPN are used to give the top 2 per Maincategory/Subcategory/Device.
   

   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] )
   )

2. Then create a measure that chooses between them depending which column is filtered. The logic here assumes you will only filter one of these columns at a time, but you could change this to handle cases where more than one is filtered.
   

   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]
   )

[javedbh]

@OwenAuger Great work, worked like a charm, Thanks.

Two more things if possible:

1. Need the data points sorted in each category
2. Remove the gaps between bars if a bin name is missing in category (see the figure for reference).

[OwenAuger]

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].