Forum Discussion

alejanchav93's avatar
alejanchav93
New Member
1 year ago

RANKX Agency Error

I'm trying to calculate an agency's ranking using the RANKX formula, but I can't figure it out.

What I am trying to do is for the ranking to be calculated taking into account the investment of each agency, but this cannot consider agencies with negative YoY, they must be excluded from the ranking. I only want a ranking of Agencies that have a positive % variation (2023 vs 2024).


I am using the following formula but without much success.

RANKX Agencia 1 Pos =
VAR InversionYTD = [Inversión neta YTD Agencia]
VAR VariacionYTD = [Variación YTD Agencia]
RETURN
IF(
NOT ISBLANK(InversionYTD) && InversionYTD <> 0 && VariacionYTD > 0,
RANKX(
            FILTER(
                           ALLSELECTED('Industria'[Agencia]),
                                   [Variación YTD Agencia] > 0
                       ),
           CALCULATE(
                                  [Inversión neta YTD Agencia],
                                             REMOVEFILTERS('Industria'[Anunciante]), -- Delete filters of advertiser column
                             ),
                             ,
            DESC,
           Dense
                      ),
           BLANK()
)

 

 

 

The expected result would be a matrix visual object like the one in the image.



Expected result

AnuncianteAgencia20232024DiffYoYRankx
TECNOQUIMICASSANCHO/BBDO$ 364,529$ 435,109$ 70,58019%1
CENTRAL CERVECERASANCHO/BBDO$ 180$ 32,094$ 31,91517744%1
PEPSI COL+POSTOBONSANCHO/BBDO$ 3,856$ 20,385$ 16,529429%1
ALMACENES EXITOSANCHO/BBDO$ 65,194$ 79,979$ 14,78523%1
BANCOLOMBIASANCHO/BBDO$ 45,218$ 59,528$ 14,30932%1
ALIMENTOS POLARSANCHO/BBDO$ 14,610$ 26,580$ 11,97082%1
MERCADOLIBRE.COMSANCHO/BBDO$ 14,966$ 24,951$ 9,98667%1
PEPSICO ALIMENTOSSANCHO/BBDO$ 11,078$ 17,517$ 6,43958%1
ECOPETROLSANCHO/BBDO$ 8,340$ 12,604$ 4,26451%1
LEADERS+CEETSANCHO/BBDO$ 2,234$ 5,461$ 3,226144%1
C FUNCIONAL BEBIDASSANCHO/BBDO$ 965$ 3,937$ 2,972308%1
DAIMLER COLOMBIA SASANCHO/BBDO$ 2,167$ 5,033$ 2,865132%1
TERPELSANCHO/BBDO$ 1,763$ 4,196$ 2,433138%1
AVON CALLINGSSANCHO/BBDO$ 268$ 1,493$ 1,225456%1
ARTURO CALLESANCHO/BBDO$ 301$ 1,380$ 1,079359%1
CORONASANCHO/BBDO$ 2,397$ 2,782$ 38516%1
L&C S.A.SSANCHO/BBDO$ 892$ 1,238$ 34639%1
PJ COL SASSANCHO/BBDO$ 478$ 812$ 33470%1
ALM EXITO+B COLPATSANCHO/BBDO$ 83$ 230$ 147176%1
COLGATE PALMOLIVEYOUNG & RUBICAM$ 159,954$ 257,348$ 97,39461%2
COLGATE PALMOLIV INTYOUNG & RUBICAM$ 9,363$ 31,787$ 22,424240%2
HUAWEI TECHNOLOGIESYOUNG & RUBICAM$ 541$ 3,313$ 2,772512%2
DERCO SAYOUNG & RUBICAM$ 3,175$ 4,147$ 97231%2
WALT DISNEY COMPANYBTL$ 145,513$ 254,319$ 108,80675%3



I have the following table

Sample Data1.xlsx

"DAX " "DAX help" 'Need help'

1 Reply

  • mark_endicott's avatar
    mark_endicott
    Icon for Super User rankSuper User
    1 year ago

    Try this:

     

    VAR InversionYTD = [Inversión neta YTD Agencia]
VAR VariacionYTD = [Variación YTD Agencia]
RETURN
IF(
NOT ISBLANK(InversionYTD) && InversionYTD <> 0 && VariacionYTD > 0,
RANKX(
            FILTER(
                           ALLSELECTED('Industria'[Agencia], 'Industria'[Anunciante]),
                                   [Variación YTD Agencia] > 0
                       ),
           CALCULATE(
                                  [Inversión neta YTD Agencia],
                                             REMOVEFILTERS('Industria'[Anunciante]), -- Delete filters of advertiser column
                             ),
                             ,
            DESC,
           Dense
                      ),
           BLANK()
)

     

    Hopfully adding the addtional column from your table into the ALLSELECTED will allow for the ranking to be calculated at the right level. 

     

    If this works, please accept as the solution to help others with the same challenge.