cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Drewdel Regular Visitor
Regular Visitor

Top N with Drill Down

Hi everyone, I'm having a difficult time trying to figure out how to accomplish what I want.  Firstly, I appologize because I can't really provide a data set due to sensitivity but here are the main columns I'm working with: Territories, Customer IDs and Sales; a Territory can have multiple Customers, and a Customer can have multiple Sales.

 

So I've worked with TopN and TopN "What If" slicers before but can't get this to work so here's what I'm trying to set up.  I'm currently trying to do is set up a column chart with both Territories (parent) and Customer IDs (child) on the axis and the sum of Sales in the value, and when you use the TopN slicer to say 3, the char will filter the top 3 Territories with the top 3 Customers per Territory.  The chart would have the X-Axis Concatenate Labels set to OFF like you could with dates in it instead.

I can't seem to figure out how to get this done either through some kind of model setup or, with what I've mainly been doing, through measures.  Is what I'm trying to do even possible and if so could someone help me, because I think this kind of visualization would be really nice and suit some of my company's needs?

1 ACCEPTED SOLUTION

Accepted Solutions
lc_finance Senior Member
Senior Member

Re: Top N with Drill Down

Hi @Drewdel ,

 

Here is the solution:

 

1) Create a parameter to be used as a slicer. You can do that by clicking on Modeling -> New parameter

2) Create a calculated measure to show the sales only if the territory and the customer is in the Top N. The formula is below

 

Filtered Sales = 

VAR topTerritoriesTable = CALCULATETABLE(
TOPN ([TopNSlicer Value], VALUES( Sheet1[Territory] ),
CALCULATE ( SUM ( 'Sheet1'[Sales] ) ))
, ALL('Sheet1'))

VAR topCustomersTable = CALCULATETABLE(
TOPN ([TopNSlicer Value], VALUES( Sheet1[CustomerID] ),
CALCULATE ( SUM ( 'Sheet1'[Sales] ) ))
, ALLEXCEPT('Sheet1','Sheet1'[Territory]))

VAR currentTerritory = SELECTEDVALUE('Sheet1'[Territory])
VAR topTerritorySelected = CONTAINS ( topTerritoriesTable, [Territory], currentTerritory )
VAR currentCustomer = SELECTEDVALUE('Sheet1'[CustomerID])
VAR topCustomerSelected = CONTAINS(topCustomersTable, [CustomerID], currentCustomer)


RETURN
IF (topTerritorySelected && topCustomerSelected, SUM('Sheet1'[Sales]), BLANK())

Here is a picture of what it looks like:

Top N Slicer.png

 

And here is a link to the finalized file

 

Hope this helps you!

 

LC

Interested in Power BI templates? Check out my blog at www.finance-bi.com

View solution in original post

6 REPLIES 6
lc_finance Senior Member
Senior Member

Re: Top N with Drill Down

Hi @Drewdel ,

 

 

I understand you cannot share sensitive information, but maybe you can recreate a very small model using sampled data? That would make it easier to understand your issue and help you.

 

Let me know,

 

LC

Drewdel Regular Visitor
Regular Visitor

Re: Top N with Drill Down

I got around to making a mock up file with the basic data I'm working with, Territories, Customers and Sales.  It's all fake but those three columns are the only ones I'm actually working with in my official report.  I haven't done anything in this mock file because I'm busy with something else but if there's anymore needed then I'll add it later.  Hope this helps.

lc_finance Senior Member
Senior Member

Re: Top N with Drill Down

Hi @Drewdel ,

 

Here is the solution:

 

1) Create a parameter to be used as a slicer. You can do that by clicking on Modeling -> New parameter

2) Create a calculated measure to show the sales only if the territory and the customer is in the Top N. The formula is below

 

Filtered Sales = 

VAR topTerritoriesTable = CALCULATETABLE(
TOPN ([TopNSlicer Value], VALUES( Sheet1[Territory] ),
CALCULATE ( SUM ( 'Sheet1'[Sales] ) ))
, ALL('Sheet1'))

VAR topCustomersTable = CALCULATETABLE(
TOPN ([TopNSlicer Value], VALUES( Sheet1[CustomerID] ),
CALCULATE ( SUM ( 'Sheet1'[Sales] ) ))
, ALLEXCEPT('Sheet1','Sheet1'[Territory]))

VAR currentTerritory = SELECTEDVALUE('Sheet1'[Territory])
VAR topTerritorySelected = CONTAINS ( topTerritoriesTable, [Territory], currentTerritory )
VAR currentCustomer = SELECTEDVALUE('Sheet1'[CustomerID])
VAR topCustomerSelected = CONTAINS(topCustomersTable, [CustomerID], currentCustomer)


RETURN
IF (topTerritorySelected && topCustomerSelected, SUM('Sheet1'[Sales]), BLANK())

Here is a picture of what it looks like:

Top N Slicer.png

 

And here is a link to the finalized file

 

Hope this helps you!

 

LC

Interested in Power BI templates? Check out my blog at www.finance-bi.com

View solution in original post

Super User
Super User

Re: Top N with Drill Down

Hi,

This is not an ideal solution because one has to apply 2 filter but it may serve as an alternative solution that you want to refine.  You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Drewdel Regular Visitor
Regular Visitor

Re: Top N with Drill Down

THANK YOU!!!  I've been wracking by brain over the weekend and couldn't find anyone online who has done this kind of thing before, and was starting to think it's not possible to do in measures.  All I had to do was add my filtered columns with the FILTER function and a switch at the end for all total if 0 and now it's exactly what I was trying to do.  Thanks again!

lc_finance Senior Member
Senior Member

Re: Top N with Drill Down

Hi @Drewdel ,

 

I am very glad to hear that it's working for you!

 

Best of luck with your analysis. If you need any additional help, let me know!

 

LC

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)