Top N with Drill Down

[Drewdel]

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?

[lc_finance]

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]

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.

[Ashish_Mathur]

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.

[lc_finance · 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:

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

[Drewdel]

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]

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