topic Re: More than 1 Rankx in Custom Visuals Development Discussion

More than 1 Rankx

Anonymous — Thu, 09 Dec 2021 14:18:48 GMT

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)

Re: More than 1 Rankx

lbendlin — Fri, 10 Dec 2021 23:31:27 GMT

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

Re: More than 1 Rankx

Anonymous — Mon, 13 Dec 2021 16:00:38 GMT

Thank you lbendlin

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

Re: More than 1 Rankx

lbendlin — Tue, 14 Dec 2021 13:08:58 GMT

did you implement my suggestion?

Re: More than 1 Rankx

Anonymous — Tue, 14 Dec 2021 19:36:32 GMT

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

Re: More than 1 Rankx

lbendlin — Wed, 15 Dec 2021 00:36:51 GMT

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

Re: More than 1 Rankx

Anonymous — Wed, 15 Dec 2021 12:41:15 GMT

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.

Re: More than 1 Rankx

Anonymous — Thu, 16 Dec 2021 20:57:30 GMT

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.

Re: More than 1 Rankx

lbendlin — Thu, 16 Dec 2021 21:10:31 GMT

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"

Re: More than 1 Rankx

Anonymous — Thu, 16 Dec 2021 21:18:43 GMT

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

Re: More than 1 Rankx

lbendlin — Thu, 16 Dec 2021 21:24:50 GMT

Add a calculated column instead

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

Re: More than 1 Rankx

Anonymous — Fri, 17 Dec 2021 18:27:28 GMT

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.

Re: More than 1 Rankx

Anonymous — Sun, 19 Dec 2021 16:21:52 GMT

Any thoughts .... anyone???