Forum Discussion

Georgia_H's avatar
Georgia_H
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's avatar
    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

SpartaBI's avatar
SpartaBI
Community Champion
4 years ago

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's avatar
Georgia_H
Helper I
4 years ago

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

 

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    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's avatar
      Georgia_H
      Helper I
      4 years ago

      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
      • SpartaBI's avatar
        SpartaBI
        Community Champion
        4 years ago

        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