RANKX Returning All as 1

[paguy215]

I'm trying to rank agents based on their sales over a selected date range.  At first it was takiing too much memory to even bring on the visual

so i ended up creating a new query to aggregate things there, and I can get the RANKX on the visual fairly easily...however now everything is ranked as 1

here is what i have

Revenue Rank =

RANKX( ALLSELECTED('Agent List'[AgentID]), calculate(sum(Query1[SalesSum])))

The fields in the 'Query1' Are a date, agent ID (that ties to the 'Agent List' table), and two columns for two different sale types, with 'SalesSum' being the calculated column adding those together.

Any help is appreciated.

[v-linhuizh-msft]

Thanks for the reply from Greg_Deckler.

Hi @paguy215 ,

Make sure you are using the AgentID field from the Agent List table in the visual, it returns the correct result in my test.

[paguy215]

Thanks, but still doesn't work and I'm using the right fields.   Also in what you shared, I see the ranks are still off (IE two rows ranked 1st but with different SalesSum totals.

Also, I wouldn't have a row for each date...the idea would be a row for each agent, summing the total within a range of dates

[Greg_Deckler]

@paguy215 RANKX is notoriously finicky. I would try using RANK instead. Or, see if this helps:

[mlozano]

But in case you need to further condition the range

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