cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Top N and the rest with Drillthrough

Good afternoon,

I am trying to get drillthrough to work from a TopN and Remaining view (show the top 5 customers and group the rest under "Remaining").  The grouping and reporting is working fine thanks to these posts.

https://www.oraylis.de/blog/show-top-n-and-rest-in-power-bi

https://www.minceddata.info/2018/06/06/topsomething-and-all-the-rest-called-other/

But I cannot get the drillthrough to work.  This is because the table that is created to hold the list of things + other is not connected to any of the fact tables.  I don't think there is a way to get drillthrough to work with the disconnected table but I wanted to ask.

The linked model has a sales table and the topn table as well as topn customer used in the visuals.  It also has a drillthrough page set up for testing.

 

Sample PBIX file: https://www.dropbox.com/s/iaycve8l1ea9vxl/TopN%20and%20Drillthrough.pbix?dl=0

 

When I drillthrough on a customer with 3.3 M in sales I get the entire 15.3 M on the drillthrough:

drillthrough example.png

Anyone have any thoughts?

 

Thank you,

John

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Top N and the rest with Drillthrough

Hi @jdbuchanan71 ,

 

Since you have disconnected table when you make the drilltrough it's not filtering any value since they don't have a relationship.

 

Do the following:

  • Create an inactive relationship between Customer table and ctCustomers by the column description
  • Create the following measure to use on your drilltrough table:
Sales Measure =
CALCULATE (
    SUM ( Sales[Sales] );
    USERELATIONSHIP ( ctCustomers[Customer Description]; Customers[Customer Description] )
)
  • Add the measure to your drilltrough table and filter to only show non blank values

Result is below and attach PBIX file .

 

 

drilltrough.gif

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




3 REPLIES 3
Super User
Super User

Re: Top N and the rest with Drillthrough

Hi @jdbuchanan71 ,

 

Since you have disconnected table when you make the drilltrough it's not filtering any value since they don't have a relationship.

 

Do the following:

  • Create an inactive relationship between Customer table and ctCustomers by the column description
  • Create the following measure to use on your drilltrough table:
Sales Measure =
CALCULATE (
    SUM ( Sales[Sales] );
    USERELATIONSHIP ( ctCustomers[Customer Description]; Customers[Customer Description] )
)
  • Add the measure to your drilltrough table and filter to only show non blank values

Result is below and attach PBIX file .

 

 

drilltrough.gif

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Top N and the rest with Drillthrough

Thank you @MFelix for the answer and including both the .pbix and video!  It seems there are two kinds of things in DAX.  Those that can be done and those that you just don't know how to do yet.

Super User
Super User

Re: Top N and the rest with Drillthrough

Hi,

In DAX there are a lot of little things especially when you are referring to measure that are based on context so just changing a small detail it's enough to make or break a calculation.

On your case what I did was to setup the context of the customer that didn't existed with disconnected tables so not being abnlw to filter from main table to drill through, bu of you had an active relation your Top N would not work.

Workaround is to activate the relationship when you need it so on the detail table using the measure.

Regards
MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 268 members 2,991 guests
Please welcome our newest community members: