cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

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:

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

6 REPLIES 6
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.

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Solution Sage

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:

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!

Solution Sage

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

Announcements

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Power BI Monthly Update - February 2024

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

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors