Forum Discussion
How to do RankX on multiple columns ?
Georgia_H this is for a measure:
Rank Measure = VAR _value_test = NOT ISBLANK (CALCULATE(SUM('rep F_ITS_MetricsLanding'[Compliance_Num]))) VAR _result = RANKX( ALLSELECTED('rep F_ITS_MetricsLanding'[LBU]), CALCULATE(SUM('rep F_ITS_MetricsLanding'[Compliance_Num])) ) RETURN IF( _value_test, _result )
Hi SpartaBI
Please refer to my desired result on column RANK.
My requirement is:
1.For April, I want to rank the best performing BU for Metric ID : PCM03, in ASIA & AFRICA regions in DESC mode
1.For April, I want to rank the best performing BU for Metric ID : PCM04, in ASIA & AFRICA regions in DESC mode and the same for May month.
| Period | Region | BU | Metric_ID | Domain | Value | Rank |
| 28-Apr | ASIA | EISG | PCM03 | Config Mgt | 0 | 2 |
| 28-Apr | ASIA | EISG | PCM04 | Config Mgt | 100 | 1 |
| 28-Apr | ASIA | PACS | PCM03 | Config Mgt | 80 | 1 |
| 28-Apr | ASIA | PACS | PCM04 | Config Mgt | 43.7255 | 2 |
| 28-Apr | ASIA | PAMB | PCM03 | Config Mgt | 49.0197 | 3 |
| 28-Apr | AFRICA | PBGI | PCM03 | Config Mgt | 62 | 2 |
| 28-Apr | AFRICA | PBLIT | PCM03 | Config Mgt | 99 | 1 |
| 31-May | ASIA | PAMB | PCM04 | Config Mgt | 97.1961 | 2 |
| 31-May | ASIA | EISG | PCM03 | Config Mgt | 0 | 3 |
| 31-May | ASIA | EISG | PCM04 | Config Mgt | 100 | 1 |
| 31-May | ASIA | PACS | PCM03 | Config Mgt | 60 | 1 |
| 31-May | ASIA | PACS | PCM04 | Config Mgt | 13.7255 | 4 |
| 31-May | ASIA | PAMB | PCM03 | Config Mgt | 49.0197 | 2 |
| 31-May | ASIA | PVA | PCM04 | Config Mgt | 89 | 3 |
Georgia_H in your sample data, why is this the result of the rank?
80 1st place
0 2nd place
3 3rd place
?
You can also PM me and we could do a quick zoom if you want
HI SpartaBI
Sorry for the typo in the sample output.
I have corrected it. Can you take below data for your testing? Appreciate it.
Period Region BU Metric_ID Domain Value Rank 28-Apr ASIA EISG PCM03 Config Mgt 0 3 28-Apr ASIA EISG PCM04 Config Mgt 100 1 28-Apr ASIA PACS PCM03 Config Mgt 80 1 28-Apr ASIA PACS PCM04 Config Mgt 43.7255 2 28-Apr ASIA PAMB PCM03 Config Mgt 49.0197 2 28-Apr AFRICA PBGI PCM03 Config Mgt 62 2 28-Apr AFRICA PBLIT PCM03 Config Mgt 99 1 31-May ASIA PAMB PCM04 Config Mgt 97.1961 2 31-May ASIA EISG PCM03 Config Mgt 0 3 31-May ASIA EISG PCM04 Config Mgt 100 1 31-May ASIA PACS PCM03 Config Mgt 60 1 31-May ASIA PACS PCM04 Config Mgt 13.7255 4 31-May ASIA PAMB PCM03 Config Mgt 49.0197 2 31-May ASIA PVA PCM04 Config Mgt 89 3 Georgia_H create this calculated column:
Rank CC = VAR _period = 'Table'[Period] VAR _region = 'Table'[Region] VAR _metric = 'Table'[Metric_ID] VAR _domain = 'Table'[Domain] VAR _result = RANKX( FILTER( 'Table', 'Table'[Period] = _period && 'Table'[Region] = _region && 'Table'[Metric_ID] = _metric && 'Table'[Domain] = _domain ), 'Table'[Value] ) RETURN _resultHi SpartaBI
Thank you for the solution. We can achieve the inteded results in the sample data given earlier. However, i tested with another set of sample data, the results are incorrect after adding another filter by Severity. See the Rank column results for Severity=S2.
Month-Year rep D_ITS_KRIList.Metric_ID REGION LBU Severity rep D_ITS_KRIList.Domain Compliance_Num Rank Jun-22 PCM03 ASIA EIKR S2 PPCM 0 12 Jun-22 PCM03 ASIA PBTB S2 PPCM 0 12 Jun-22 PCM03 ASIA PLUK S2 PPCM 0 12 Jun-22 PCM03 ASIA EISG S2 PPCM 100 8 Jun-22 PCM03 ASIA PAMB S2 PPCM 100 8 Jun-22 PCM03 ASIA PTPLA S2 PPCM 100 8 Jun-22 PCM03 ASIA PVA S2 PPCM 100 8 Jun-22 PCM03 ASIA PSS S2 PPCM 16.6667 7 Jun-22 PCM03 ASIA PHKL S2 PPCM 33.3334 6 Jun-22 PCM03 ASIA EIMB S2 PPCM 50 4 Jun-22 PCM03 ASIA PSA S2 PPCM 50 4 Jun-22 PCM03 ASIA PCA S2 PPCM 75 3 Jun-22 PCM03 ASIA PACS S2 PPCM 80 2 Jun-22 PCM03 ASIA EIKR S1 PPCM 100 1 Jun-22 PCM03 ASIA EIMB S1 PPCM 100 1 Jun-22 PCM03 ASIA EISG S1 PPCM 100 1 Jun-22 PCM03 ASIA PACS S1 PPCM 100 1 Jun-22 PCM03 ASIA PAMB S1 PPCM 100 1 Jun-22 PCM03 ASIA PBTB S1 PPCM 100 1 Jun-22 PCM03 ASIA PCA S1 PPCM 100 1 Jun-22 PCM03 ASIA PCALT S1 PPCM 100 1 Jun-22 PCM03 ASIA PCALT S2 PPCM 85.7143 1 Jun-22 PCM03 ASIA PHKL S1 PPCM 100 1 Jun-22 PCM03 ASIA PLUK S1 PPCM 100 1 Jun-22 PCM03 ASIA PSA S1 PPCM 100 1 Jun-22 PCM03 ASIA PSS S1 PPCM 100 1 Jun-22 PCM03 ASIA PTPLA S1 PPCM 100 1 Jun-22 PCM03 ASIA PVA S1 PPCM 100 1 My dax calculated column: