More than 1 Rankx

[vrajkumar1]

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, sort service group descending and then pick top 20

For Service Detail - I created a measure using the following DAX

Rank_SvcDtl =

IF (

ISINSCOPE('Table' [Service Detail]),

RANKX(

ALLSELECTED('Table' [Service Detail]),

CALCULATE(SUM('Table' [Gross Revenue]),

FILTER(ALL('Table' [Gross Revenue]),'Table' [Gross Revenue]<>BLANK())),,ASC,Dense))

I pulled this measure and filtered on less than 21. Here was the problem.

Not all items were showing 20. Some only showed 19.  So if I increased my filter number then my Gross Revenue was off for some Groups.

For Service Group - I created the following measure

Rank_SvcGrpRk2 =

IF(

ISINSCOPE('Table' [Service Group]),

RANKX(

ALLSELECTED('Table' [Service Group]),

CALCULATE(SUM('Table' [Gross Revenue]),

FILTER(ALL('Table' [Gross Revenue]),

Table' [Gross Revenue]<>BLANK())),,DESC,Dense))

Same problem as Service Detail.  The total Gross Revenue was off.

I tried using SKIP instead of DENSE.  That didn’t help.

I know I am on the right path.  I have to create Rank measures, and then filter or maybe for one of them I need to use TOP N.

I am just not able to narrow down on what is causing the issue.

I have been at this for a few days.  Hope someone can shed some light on this.

Also - if I were to add the Clinics and just get Top 10 - would that again be a new measure.

THANK YOU for looking into this.

Clinics	Service Group	Service Detail	Gross Revenue
Clinic 1	52 - Chemotherapy	Detail Ch	$3,419k
Clinic 1	52 - Chemotherapy	Detail RT	($1k)
Clinic 1	32 - Grouper D	Detail E	$420k
Clinic 1	32 - Grouper D	Detail PPS	$397k
Clinic 1	32 - Grouper D	Detail F	$320k
Clinic 1	32 - Grouper D	Foot	$201k
Clinic 1	32 - Grouper D	Sports Medicine	$114k
Clinic 1	54 - Infusion Therapy	Detail InT	$919k
Clinic 1	54 - Infusion Therapy	Detail Ch	$201k
Clinic 1	53 - Radiation Therapy	Detail RT	$114k
Clinic 1	19 - Pacemaker	Detail E	$919k
Clinic 2	54 - Infusion Therapy	Detail P	$45k
Clinic 2	54 - Infusion Therapy	Detail V	$19k
Clinic 2	54 - Infusion Therapy	Miscellaneous Services	($1k)
Clinic 2	54 - Infusion Therapy	Detail Ch	($20k)
Clinic 2	53 - Radiation Therapy	Detail RT	$871k
Clinic 2	19 - Pacemaker	Detail E	$613k
Clinic 2	56 - Hyperbaric	Wound Care	$428k
Clinic 2	76 - Lab and Pathology	Detail O	$187k
Clinic 2	76 - Lab and Pathology	Detail EM	$9k
Clinic 2	76 - Lab and Pathology	Detail TM	$3k
Clinic 2	76 - Lab and Pathology	Detail V	$2k
Clinic 2	76 - Lab and Pathology	Detail HM	$1k
Clinic 2	76 - Lab and Pathology	PSA Test	$1k
Clinic 2	76 - Lab and Pathology	Chemistry	$0k
Clinic 2	76 - Lab and Pathology	Detail U	$0k
Clinic 3	52 - Chemotherapy	Detail Ungrp	($0k)
Clinic 3	52 - Chemotherapy	Detail RT	($1k)
Clinic 3	32 - Grouper D	Detail E	$420k
Clinic 3	32 - Grouper D	Detail PPS	$397k
Clinic 3	32 - Grouper D	Detail F	$320k
Clinic 3	32 - Grouper D	Foot	$201k
Clinic 3	32 - Grouper D	Sports Medicine	$114k
Clinic 4	77 - All Other OP	Detail I	$919k
Clinic 4	77 - All Other OP	Detail EM	$45k
Clinic 4	77 - All Other OP	Detail Ungrp	$19k
Clinic 4	77 - All Other OP	Detail InT	($1k)
Clinic 4	73 - Other Diagnostic Radiology	Medical Cardiology	($1k)
Clinic 4	19 - Pacemaker	Detail E	$613k
Clinic 4	56 - Hyperbaric	Wound Care	$428k
Clinic 5	32 - Grouper D	Detail F	$320k
Clinic 5	32 - Grouper D	Foot	$201k
Clinic 5	32 - Grouper D	Sports Medicine	$114k
Clinic 5	54 - Infusion Therapy	Detail InT	$919k
Clinic 5	54 - Infusion Therapy	Detail P	$45k
Clinic 5	54 - Infusion Therapy	Detail V	$19k
Clinic 5	54 - Infusion Therapy	Miscellaneous Services	($1k)
Clinic 5	77 - All Other OP	Detail I	$919k
Clinic 5	77 - All Other OP	Detail EM	$45k
Clinic 5	77 - All Other OP	Detail Ungrp	$19k
Clinic 5	77 - All Other OP	Detail InT	($1k)

[vrajkumar1]

Thank you @lbendlin 

Any suggestions on - why not all 20 are showing.  Some DETAIL and also SERVICE GROUP show only 19.

[lbendlin]

did you implement my suggestion?

[vrajkumar1]

@lbendlin  Hi

I tried - but keep erroring out.  Here is my Rank measure.  Not sure where I would add RAND()

Rank_SvcDtl =

IF (

    ISINSCOPE(‘TABLE’[SVC_DTL]),

    RANKX(

        ALL(‘TABLE’[SVC_DTL]),     

        CALCULATE(SUM(‘TABLE’[Gross Rev Var]),

            FILTER(ALL(‘TABLE’[Gross Rev Var]),’TABLE’[Gross Rev Var]<>BLANK())),,DESC,Dense))

[lbendlin]

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

[vrajkumar1]

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. 

[vrajkumar1]

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]

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"

[vrajkumar1]

@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).

[lbendlin]

Add a calculated column instead 

GR = 'Table'[Gross Revenue]+RAND()/1000

[vrajkumar1]

@lbendlin 

The following is almost working

Rank_SvcDtl =

IF (

    ISINSCOPE(‘TABLE’[SVC_DTL]),

    RANKX(

        ALL(‘TABLE’[SVC_DTL]),     

        CALCULATE(SUM(‘TABLE’[GrossRev2]),

        FILTER(ALL(‘TABLE’[GrossRev2]),’TABLE’[GrossRev2]<>BLANK())),,ASC,Dense))

GrossRev2 = ‘TABLE’[Gross Rev Var] + (RAND()/1000)

here is the problem - (the following is similar fields, but different numbers)

Rank 12 is missing.  This is throwing the total off, and then of course the sort order is off too.

How can I tweak the dax, to make sure there is no skip in rank.  I think that should solve the problem.

[vrajkumar1]

Any thoughts .... anyone???

[lbendlin]

Add a tiny random value (thousandths of cents) to your values to break the ties.