Forum Discussion

Zerozinho's avatar
Zerozinho
New Member
5 years ago
Solved

How do I rank inside many segments?

Hey, I'm not experienced with PowerBI and I'm failing to rank the following data:   Name Group Segment Value Rank Max Rank Jorge 1 Small 9 1 1 Jorge 1 Medium 9 1 2 G...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Zerozinho ,

     

    You can use this DAX expression to Rank groups and segments.

     

     

    Ranki = RANKX(FILTER(ALL('Table'),'Table'[Segment] = MAX ('Table'[Segment]) && 'Table'[Group] = MAX ('Table'[Group])),CALCULATE(Sum('Table'[Value])))

     

     

     

     

    For MAX Rank you can use the following DAX

     

    Counti = COUNTX(FILTER(ALL('Table'),'Table'[Segment] = MAX ('Table'[Segment]) && 'Table'[Group] = MAX ('Table'[Group])),CALCULATE(Sum('Table'[Value])))

     

    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous's avatar
Anonymous
Not applicable
5 years ago

 

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sovSk9V0lEyBOLg3MScHCBtCeUbKsXqoKrwTU3JLM1FUmIEVuKemFSUmZqDrsgCrACmyDexKDMRKgKzyQSIjcEYXQXcEBMUxyArccpMB5KmKPLOiUU5+cVotphB+RBbHPNKMvLzKtHUmEONgajxyS/NLIa6DWKNMcKaWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Group = _t, Segment = _t, Value = _t, Rank = _t, #"Max Rank" = _t]),
    #"Raggruppate righe" = Table.Group(Origine, {"Group", "Segment"}, {{"MaxRankCalc", each List.Max([Rank]), type nullable text}}),
    #"Merge di query eseguito" = Table.NestedJoin(Origine, {"Group", "Segment"},  #"Raggruppate righe", {"Group", "Segment"}, "hhh"),
    #"Tabella hhh espansa" = Table.ExpandTableColumn(#"Merge di query eseguito", "hhh", {"MaxRankCalc"}, {"MaxRankCalc"})
in
    #"Tabella hhh espansa"