Forum Discussion

newpbiuser01's avatar
newpbiuser01
Helper V
2 years ago
Solved

Dynamically Rank Table By Different Groups

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 2...
Need Help
aduguid's avatar
aduguid
Memorable Member
2 years ago

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"