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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jajoerger
Frequent Visitor

Top N and "Other" drill down

I have a Matrix with monthly values and a list of Clients.  I need the Top 10 and Other.  I also need to be able to drill down into each Client and add the employee who attributes values to the total for the month.  My Top 10 works well using the Ranking.  

jajoerger_0-1710181589534.png

When I add the Employee to the Row to drill down, I no longer see the Top 10.  How can I keep it to the Top 10 & Other?

jajoerger_1-1710181758607.png

Here is my Ranking Measure:

Ranking 2023 =
VAR HoursToRank = [TopN Value]
VAR Ranking =
 RANKX(
    ALLSELECTED(Clients_Other[Clients]),
    [2023TotalHrs]
    )
VAR IsOtherSelected = SELECTEDVALUE('Clients_Other'[Clients]) = "Others"
VAR Result =
IF(
    IsOtherSelected,
    HoursToRank + 1,
    IF(Ranking <= HoursToRank, Ranking, Ranking + 1)
)
RETURN
Result
 
Here are my values for a given month:
2024_06 =
VAR HoursOfAll =
CALCULATE('TableValues'[2024_06Hrs],REMOVEFILTERS(Clients_Other[Clients]))
RETURN
IF(
    ISINSCOPE(Clients_Other[Clients]),
    VAR ClientToRank = 'TopN'[TopN Value]
    VAR IsOtherSelected = SELECTEDVALUE(Clients_Other[Clients]) = "Others"
    VAR ClientsWithHoursAmt =
    ADDCOLUMNS(
        ALLSELECTED(Clients_Other[Clients])
        ,"@Amt", ([2024_06Hrs])
    )
    Var Top15Clients = TOPN(ClientToRank,ClientsWithHoursAmt,[@Amt])
    VAR HoursOfTop15 = SUMX(Top15Clients,[@Amt])
    VAR Result = IF(IsOtherSelected,HoursOfAll - HoursOfTop15, [2024_06Hrs])
    RETURN Result,
     HoursOfAll
)
 
jajoerger_2-1710181963210.png

 

2 REPLIES 2
lbendlin
Super User
Super User

you probbaly don't want to have to specify all these different values separately. Better to use a single measure.

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

My data is located here:

https://www.dropbox.com/scl/fo/dbf1f6kiuzmge3ietar3w/h?rlkey=9pqlz66wiwxmn04kyf6zrr2xt&dl=0

 

I'd like the matrix to look like the following if you drill into Client.  My goal is to show TopN Clients (eg. 3) and then all other clients should fall into to the Other bucket.  When a user clicks on Client, they should be able to see Emps with values that make up the Client Total.    Other needs to be the last line or under the Top N Clients.  In my example I have Rankin Asc order so that the Client with the largest Total is listed first.

jajoerger_0-1710205799177.png

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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