Forum Discussion

Helper I

4 years ago

Solved

How to do RankX on multiple columns ?

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

Need Help

SpartaBI

4 years ago

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

Georgia_H

Helper I

4 years ago

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

Community Champion

4 years ago

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

Community Champion

4 years ago

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

Forum Discussion

How to do RankX on multiple columns ?

Related content

Calculated column RANKX with multiple columns

rankx multiple columns allselected

RANKX Multiple Columns

RANKX with Multiple Criterias

Sequential Order column based on multiple criteria