How to do RankX on multiple columns ?

[Georgia_H]

Hi all, i am struggling with RANKX funtion on multiple columns. All the columns are from the same table.

I want to rank across different reporting Period, Region, BU, Metric_ID, Domain based on Value column in DESC order.

Could anyone help me with the syntax to achieve my requirement?  Many thanks in advance.

[SpartaBI]

@Georgia_H what is the result you want to get? 
Can you share a sample copy paste table and hard code the result and the logic for it.

[Georgia_H]

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

[SpartaBI]

@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

[Georgia_H]

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

[SpartaBI]

@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

      

Showcase Report – Contoso By SpartaBI

[Georgia_H]

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: 

[SpartaBI]

@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/2449543

      

Showcase Report – Contoso By SpartaBI

[SpartaBI · Accepted solution]

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

      

Showcase Report – Contoso By SpartaBI