Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
javedbh
Helper II
Helper II

Top N with multiple drill down

Here is my data:

 

MainCategory-SubCategory-Device-Lot-Bin_Name-Bin_Count
Cat1SubCat1Dev1Lot1Bin 3016
Cat1SubCat1Dev1Lot1Bin 3122
Cat1SubCat1Dev1Lot2Bin 3020
Cat1SubCat1Dev1Lot2Bin 3121
Cat1SubCat1Dev1Lot3Bin 2911
Cat1SubCat1Dev1Lot3Bin 3017
Cat1SubCat1Dev2Lot4Bin 1817
Cat1SubCat1Dev2Lot4Bin 308
Cat1SubCat1Dev2Lot5Bin 2920
Cat1SubCat1Dev2Lot5Bin 3021
Cat1SubCat2Dev3Lot6Bin 3018
Cat1SubCat2Dev3Lot6Bin 3130
Cat1SubCat2Dev3Lot7Bin 3042
Cat1SubCat2Dev3Lot7Bin 3122
Cat1SubCat2Dev4Lot8Bin 1837
Cat1SubCat2Dev4Lot8Bin 3011
Cat1SubCat2Dev4Lot9Bin 2927
Cat1SubCat2Dev4Lot9Bin 3028
Cat2SubCat3Dev5Lot10Bin 3017
Cat2SubCat3Dev5Lot10Bin 318
Cat2SubCat3Dev5Lot11Bin 4520
Cat2SubCat3Dev5Lot11Bin 8521
Cat2SubCat3Dev5Lot12Bin 1511
Cat2SubCat3Dev5Lot12Bin 3517
Cat2SubCat3Dev6Lot13Bin 7417
Cat2SubCat3Dev6Lot13Bin 898
Cat2SubCat3Dev6Lot14Bin 2920
Cat2SubCat3Dev6Lot14Bin 3021
Cat2SubCat4Dev7Lot11Bin 3018
Cat2SubCat4Dev7Lot11Bin 3155

 

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:

sample.png

 

How to achieve this?

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
OwenAuger
Super User
Super User

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@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).

bar gap.png

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors