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
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
_result
Hi 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:
Georgia_H I took your new sample data and added it to the file.
I'm getting different result. Please check and let me know:
How to do RankX on multiple columns 2022-07-23.pbix
If you don't succeed you can PM me and we could do a quick zoom call and look together if you want.
P.S. Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. Please give it a thumbs up over there if you liked it 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543Georgia_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 )