Dynamically Rank Table By Different Groups

[newpbiuser01]

Hello,

I have data that shows sales by team (L3), then rolls that up to sales under a bigger team (L2) which rolls up to the team at L1. 

Year	Employee	Team L1	Team L2	Team L3	Sales
2020	Bob	Team A1	Team B11	Team C111	79
2021	Sally	Team A1	Team B12	Team C112	51
2022	Sandra	Team A2	Team B21	Team C211	90
2023	Billy	Team A3	Team B31	Team C311	58
2020	Mike	Team A1	Team B12	Team C113	33
2023	Robert	Team A1	Team B12	Team C114	39
2021	Sally	Team A2	Team B22	Team C221	54
2022	Billy	Team A3	Team B33	Team C333	48
2024	Mike	Team A1	Team B11	Team C111	93
2020	Dave	Team A1	Team B12	Team C112	86
2021	Sally	Team A2	Team B21	Team C211	39
2023	Sandra	Team A3	Team B31	Team C311	57
2022	Andy	Team A1	Team B12	Team C113	29
2020	Sandra	Team A1	Team B12	Team C114	77
2021	Dave	Team A2	Team B22	Team C221	37
2022	Sally	Team A3	Team B33	Team C333	73
2023	Bob	Team A2	Team B23	Team C112	25
2023	Sandy	Team A3	Team B33	Team C311	50

I need to show a report that shows a list of all employees ranked by sales under each L1, L2 and L3 (seperately)

So, if a user selects to show the employees for 2023, for L1, it should show the Team L1, Team L2 and ranking for the employee by Team L1 (and not L2). 

Year	Employee	Team Level 1	Team Level L2	Sales	Desired Employee Rank over L1	What I'm getting
2023	Billy	Team A3	Team B31	58	1	1
2023	Sandra	Team A3	Team B31	57	2	2
2023	Sandy	Team A3	Team B33	50	3	1
2023	Bob	Team A2	Team B23	25	1	1
2023	Robert	Team A1	Team B12	39	1	1

For the same year, for ranking by L2 should show the Team L2, Team L3 (one level down), and rank the employees by L2 (ignoring L3):

Year	Employee	Team Level 2	Team Level 3	Sales	Desired Employee Rank over L2	What I'm getting
2023	Billy	Team B31	Team C311	58	1	1
2023	Sandra	Team B31	Team C311	57	2	2
2023	Robert	Team B12	Team C114	39	1	1
2023	Bob	Team B23	Team C112	25	1	1
2023	Sandy	Team B33	Team C311	50	1	3

I need to somehow explicitly ignore the one level down order while ranking. How can I do that? 

I'm using the following as my measure:

RANKX(ALLSELECTED('Table'[Employee]), [Sales],,DESC) 

and Sales = sum([Sales])

What am I doing wrong? How can I calculate the ranking for each employee by one group and ignore the other groups (while showing the other groups in the visual)? I find that if I were to only show the same group that I'm ranking by, it works, but I need to show one level down in my visual as part of my report. 

Year	Employee	Team Level 1	Sales	Desired Employee Rank over L1
2023	Billy	Team A3	58	1
2023	Sandra	Team A3	57	2
2023	Sandy	Team A3	50	3
2023	Bob	Team A2	25	1
2023	Robert	Team A1	39	1

 I'd really appreciate any help!

Thank you!

[ThxAlot · Accepted solution]

[newpbiuser01]

Thank you! This worked like a charm. Really appreciate it. 

[aduguid]

I would unpivot the team columns to make it a bit easier. Then you can add a measure for the ranking.

User Team Rank = 
VAR _current_year = MAX('Table'[Year])
VAR _current_team_group = MAX('Table'[Team Group])
VAR _current_sales = MAX('Table'[Sales])

RETURN
RANKX(
    ALLSELECTED('Table'),
    CALCULATE(
        MAX('Table'[Sales]),
        'Table'[Year] = _current_year,
        'Table'[Team Group] = _current_team_group
    ),
    _current_sales,
    DESC,
    DENSE
)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZMxD4IwEIX/iunMAHfUwgi6uqgbYYDAQERIiDHx30traK+oLQs5kn59fe+1RcEghJAFLB/r+Xttq/sui5Ypj/R4iNQsUlYGCpJ/l6rvX78wIJicebRgoLChmSrN6cU5GDlQcmm4cCjP2FE51BgaDBXGkwWT1k7drfUdUm6GSMXOY91ODx8XS86RCfFmMFALeUwz+esNjTc1x9pb7PC27i1FmsixenoTkXOy3+Rs3ZoJBL/bdtUmaCLZ0HjvltwMUmptpeaoTQhqzorE0RoK+yZvbE1Yd4s+NqKFzI4f+DpHn9YnxvnRlG8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Employee = _t, #"Team L1" = _t, #"Team L2" = _t, #"Team L3" = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Employee", type text}, {"Team L1", type text}, {"Team L2", type text}, {"Team L3", type text}, {"Sales", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Year", "Employee", "Sales"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Team Group"}, {"Value", "Team Value"}})
in
    #"Renamed Columns"