Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

More than 1 Rankx

Please see attached data. Attached screenshot is this report in Excel and the end result I am looking for.   First, sort service detail descending then pick the bottom 20 Second...
lbendlin's avatar
lbendlin
Super User
4 years ago

Seems to work for me. The only thing that's off are the ranks for the  negative values.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thank you! I eagery opened the file.  Sadly, I have (my company) a Jan 2021 version of PBI.  and hence unable to open  your file.  Is there anyway you can just post the dax here, that you used.

I had the issue with negative numbers also with a different version of dax I tried earlier.  At that time I was going to add an if statment for <0 and >0. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I also tried to open this in PowerBI Service but Unfortunately seeing DAX behind a measure is not possible in power bi service and you can only see that in pbi desktop.

    lbendlin In PBiService I can see your ranking.  Looks good.  But if I can just view the DAX that would be great.

    Thanks for all your help.

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      There is no real change in the DAX (apart from some cleanup)

       

      Rank_SvcDtl = 
IF (ISINSCOPE('Table'[Service Detail]),
    RANKX(ALL('Table'[Service Detail]),     
        CALCULATE(SUM('Table'[GR]),not ISBLANK('Table'[GR])),,DESC,Dense))

       

       

      The change is done in Power Query.

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZVdT8IwFIb/SkO40AQTug8GlwZUTCQugnphvKijQsNoSVdM+Pe2XYy6dmvLXVPOw3l3Pt6+vfWmJaGkALA36KURuALTLd4zscUcHU7yboYFIqW8led+PEjgZNd7H/hiTyt5Bhd9uLtsUrGi7jg7HjAHs1/kRiVKoqGRpg3I86XWNsm8kVsN+OS4ZUyo4GgI3cHLA+OiAgu8JgWhWHEQJma9Esnd089jRRgFq2bN7qkqWn9iq3Q3WTfJJjWNJfiE1gQJK6n7ZBULJ5LMUYH3aId5o01NjZFbY67bm4ZiL1qff7YFqQpclohidqzAEvMvUuDKOowe2XVhJSknxkS9SjvOoKG9s7QjGJsfO5LA/CTH7QNxUsibV3akazBFHNdbMzaYTDEP6AMgGZgjsWUl2/yR96gLO86CwZuFnoBgbqU58+NcnJ6AKBib63Rm8Vu5fHkNVrgSgZjyP1IJrs79YbDMZxOLu631mW74oR7LxlQ6wBZPjkM9uQto8eQuxOrJVqDNk63BHp6cqP5kkrsu5T6oeoHH/I8f25zOwdTL0bA5B/PT0KbNueRRez81prypRmYEbSirhPxFmdXP+Om6oFJZyO+l/c+C7Co5w67S0OmwAm3TYQ32mI707Bfbg7S9hh6Y7TXswgJew/SMZXAw1mVwMG3L4JL3bxnevwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Clinics = _t, #"Service Group" = _t, #"Service Detail" = _t, #"Gross Revenue" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"$","",Replacer.ReplaceText,{"Gross Revenue"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",",","",Replacer.ReplaceText,{"Gross Revenue"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","(","-",Replacer.ReplaceText,{"Gross Revenue"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",")","",Replacer.ReplaceText,{"Gross Revenue"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","k","000",Replacer.ReplaceText,{"Gross Revenue"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value4",{{"Gross Revenue", Currency.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "GR", each [Gross Revenue]+Number.Random()/100),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"GR", Currency.Type}})
in
    #"Changed Type1"

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        lbendlin 

        Thank you.  Is there a more user friendly version ğŸ™‚ ?  The PowerQuery section of the code is beyond my skillset.  Per your earlier suggestion, if I wanted to add a small random value how would you envision it in the dax formula? (so that I can break the tie).