Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Georgia_H
Helper I
Helper I

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 Value column in DESC order.

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

 

Sample data.JPG

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

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

View solution in original post

8 REPLIES 8
SpartaBI
Community Champion
Community Champion

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

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.

 

PeriodRegionBUMetric_IDDomainValueRank 
28-AprASIAEISGPCM03Config Mgt02
28-AprASIAEISGPCM04Config Mgt1001
28-AprASIAPACSPCM03Config Mgt801
28-AprASIAPACSPCM04Config Mgt43.72552
28-AprASIAPAMBPCM03Config Mgt49.01973
28-AprAFRICAPBGIPCM03Config Mgt622
28-AprAFRICAPBLITPCM03Config Mgt991
31-MayASIAPAMBPCM04Config Mgt97.19612
31-MayASIAEISGPCM03Config Mgt03
31-MayASIAEISGPCM04Config Mgt1001
31-MayASIAPACSPCM03Config Mgt601
31-MayASIAPACSPCM04Config Mgt13.72554
31-MayASIAPAMBPCM03Config Mgt49.01972
31-MayASIAPVAPCM04Config Mgt893

 

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

PeriodRegionBUMetric_IDDomainValueRank 
28-AprASIAEISGPCM03Config Mgt03
28-AprASIAEISGPCM04Config Mgt1001
28-AprASIAPACSPCM03Config Mgt801
28-AprASIAPACSPCM04Config Mgt43.72552
28-AprASIAPAMBPCM03Config Mgt49.01972
28-AprAFRICAPBGIPCM03Config Mgt622
28-AprAFRICAPBLITPCM03Config Mgt991
31-MayASIAPAMBPCM04Config Mgt97.19612
31-MayASIAEISGPCM03Config Mgt03
31-MayASIAEISGPCM04Config Mgt1001
31-MayASIAPACSPCM03Config Mgt601
31-MayASIAPACSPCM04Config Mgt13.72554
31-MayASIAPAMBPCM03Config Mgt49.01972
31-MayASIAPVAPCM04Config Mgt893

@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

 

 

SpartaBI_0-1658652394395.png

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

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-Yearrep D_ITS_KRIList.Metric_IDREGIONLBUSeverityrep D_ITS_KRIList.DomainCompliance_NumRank
Jun-22PCM03ASIAEIKRS2PPCM012
Jun-22PCM03ASIAPBTBS2PPCM012
Jun-22PCM03ASIAPLUKS2PPCM012
Jun-22PCM03ASIAEISGS2PPCM1008
Jun-22PCM03ASIAPAMBS2PPCM1008
Jun-22PCM03ASIAPTPLAS2PPCM1008
Jun-22PCM03ASIAPVAS2PPCM1008
Jun-22PCM03ASIAPSSS2PPCM16.66677
Jun-22PCM03ASIAPHKLS2PPCM33.33346
Jun-22PCM03ASIAEIMBS2PPCM504
Jun-22PCM03ASIAPSAS2PPCM504
Jun-22PCM03ASIAPCAS2PPCM753
Jun-22PCM03ASIAPACSS2PPCM802
Jun-22PCM03ASIAEIKRS1PPCM1001
Jun-22PCM03ASIAEIMBS1PPCM1001
Jun-22PCM03ASIAEISGS1PPCM1001
Jun-22PCM03ASIAPACSS1PPCM1001
Jun-22PCM03ASIAPAMBS1PPCM1001
Jun-22PCM03ASIAPBTBS1PPCM1001
Jun-22PCM03ASIAPCAS1PPCM1001
Jun-22PCM03ASIAPCALTS1PPCM1001
Jun-22PCM03ASIAPCALTS2PPCM85.71431
Jun-22PCM03ASIAPHKLS1PPCM1001
Jun-22PCM03ASIAPLUKS1PPCM1001
Jun-22PCM03ASIAPSAS1PPCM1001
Jun-22PCM03ASIAPSSS1PPCM1001
Jun-22PCM03ASIAPTPLAS1PPCM1001
Jun-22PCM03ASIAPVAS1PPCM1001

 

My dax calculated column: 

Georgia_H_0-1658719588904.png

 

@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









2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

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

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.