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
ReciMixi
Frequent Visitor

Ranking with hierarchy levels for bottom 25%

I am very new to Power Bi, I am currently having trouble to create a ranking for Bottom 25% employee. There are 3 levels of the Hierarchy: Directors, LM, and employee. I would appriciate if anyone would able to help me on this. I've tried multiple ways but it have not give me the desire result. 

I would like it to display any employee who has less and equal 25% and wont show any 0%/ blank value. 

NAME ---------------------RANK

Director 1 ----------------- 1

     LM B                              1
        EMPLY BB                   1
     LM A                              2
        EMPLY AA                   1
     LM C                               3
        EMPLY CC                    1
        EMPLY CCA                  2

Director 2                             2
     LM H                               1
        EMPLY HH                    1
I have tried the 2 following but its not given the result that I am desired:

Rank by CBTs = RANKX(ALLSELECTED('Employee'[DIRECTORS],'Employee'[LM],'Employee'[EMPLY_NM]), [CBTs% Filter],, DESC)



CODE 2:

_CBTs Ranking =
VAR _Director_Ranking =
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            Employee,
            Employee[Directors] ) ,
            "@Directors RANKING",
            RANKX(ALL(Employee[Directors]), [CBTs %], , DESC ) ) ,
            [@Directors RANKING] )

VAR _LM_Ranking =
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            Employee,
            Employee[LM] ) ,
            "@LM RANKING",
            RANKX(ALL(Employee[LM]),[CBTs %], , DESC ) ) ,
            [@LM RANKING] )

VAR _Tech_Ranking =
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            Employee,
            Employee[EMPLY_NM] ) ,
            "@EMPLY RANKING",
            RANKX(ALL(Employee[EMPLY_NM]), [CBTs %], , DESC ) ) ,
            [@EMPLY RANKING] )

VAR _Results =
    SWITCH(
        TRUE(),
        ISINSCOPE(Employee[EMPLY_NM]), _Tech_Ranking,
        ISINSCOPE(Employee[LM]), _LM_Ranking,
        ISINSCOPE(Employee[Directors]), _Director_Ranking,
        BLANK()
    )

RETURN
    _Results

Thank you so much!
1 REPLY 1
lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

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.