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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Drewdel
Advocate II
Advocate II

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

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
Solution Sage
Solution Sage

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

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.

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/

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

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!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.