RANKX with additional conditions

[mlozano]

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

[v-stephen-msft]

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

[mark_endicott · Accepted solution]

@mlozano 

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.