Forum Discussion
Helper IIIRANKX with additional conditions
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
| Anunciante | Agencia | 2023 | 2024 | Diff | YoY | Rankx |
| TECNOQUIMICAS | SANCHO/BBDO | $ 364,529 | $ 435,109 | $ 70,580 | 19% | 1 |
| CENTRAL CERVECERA | SANCHO/BBDO | $ 180 | $ 32,094 | $ 31,915 | 17744% | 1 |
| PEPSI COL+POSTOBON | SANCHO/BBDO | $ 3,856 | $ 20,385 | $ 16,529 | 429% | 1 |
| ALMACENES EXITO | SANCHO/BBDO | $ 65,194 | $ 79,979 | $ 14,785 | 23% | 1 |
| BANCOLOMBIA | SANCHO/BBDO | $ 45,218 | $ 59,528 | $ 14,309 | 32% | 1 |
| ALIMENTOS POLAR | SANCHO/BBDO | $ 14,610 | $ 26,580 | $ 11,970 | 82% | 1 |
| MERCADOLIBRE.COM | SANCHO/BBDO | $ 14,966 | $ 24,951 | $ 9,986 | 67% | 1 |
| PEPSICO ALIMENTOS | SANCHO/BBDO | $ 11,078 | $ 17,517 | $ 6,439 | 58% | 1 |
| ECOPETROL | SANCHO/BBDO | $ 8,340 | $ 12,604 | $ 4,264 | 51% | 1 |
| LEADERS+CEET | SANCHO/BBDO | $ 2,234 | $ 5,461 | $ 3,226 | 144% | 1 |
| C FUNCIONAL BEBIDAS | SANCHO/BBDO | $ 965 | $ 3,937 | $ 2,972 | 308% | 1 |
| DAIMLER COLOMBIA SA | SANCHO/BBDO | $ 2,167 | $ 5,033 | $ 2,865 | 132% | 1 |
| TERPEL | SANCHO/BBDO | $ 1,763 | $ 4,196 | $ 2,433 | 138% | 1 |
| AVON CALLINGS | SANCHO/BBDO | $ 268 | $ 1,493 | $ 1,225 | 456% | 1 |
| ARTURO CALLE | SANCHO/BBDO | $ 301 | $ 1,380 | $ 1,079 | 359% | 1 |
| CORONA | SANCHO/BBDO | $ 2,397 | $ 2,782 | $ 385 | 16% | 1 |
| L&C S.A.S | SANCHO/BBDO | $ 892 | $ 1,238 | $ 346 | 39% | 1 |
| PJ COL SAS | SANCHO/BBDO | $ 478 | $ 812 | $ 334 | 70% | 1 |
| ALM EXITO+B COLPAT | SANCHO/BBDO | $ 83 | $ 230 | $ 147 | 176% | 1 |
| COLGATE PALMOLIVE | YOUNG & RUBICAM | $ 159,954 | $ 257,348 | $ 97,394 | 61% | 2 |
| COLGATE PALMOLIV INT | YOUNG & RUBICAM | $ 9,363 | $ 31,787 | $ 22,424 | 240% | 2 |
| HUAWEI TECHNOLOGIES | YOUNG & RUBICAM | $ 541 | $ 3,313 | $ 2,772 | 512% | 2 |
| DERCO SA | YOUNG & RUBICAM | $ 3,175 | $ 4,147 | $ 972 | 31% | 2 |
| WALT DISNEY COMPANY | BTL | $ 145,513 | $ 254,319 | $ 108,806 | 75% | 3 |
I have the following table
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.
2 Replies
- mark_endicott
Super User
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.
Hi mlozano ,
Did mark_endicott's reply help your? If so, accept the reply as a solution. This will make it easier for the future people to find the answer quickly.
If not, please provide a more detailed description, preferably some error message, and the detailed data model.
Reference: Introducing RANKX in DAX - SQLBI
Best Regards,
Stephen Tao
