Forum Discussion
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, 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) |
12 Replies
Add a tiny random value (thousandths of cents) to your values to break the ties.
Thank you lbendlin
Any suggestions on - why not all 20 are showing. Some DETAIL and also SERVICE GROUP show only 19.
did you implement my suggestion?
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))
