I am trying to build a RANKX of Agencies, based on investment, but only of agencies that have positive variation, the issue is that everything gives me 1 since I am building a matrix that must be filtered by this rank to visualize the advertisers when the rank is number 1, the issue is that when I apply the filter everything gives 1 and does not bring me the correct advertisersI expect a result like the followingAdvertiserAgency20232024DiffYoyRankxCHEMICAL TECHNOLOGIESSANCHO/BBDO$ 364,529$ 435,109$ 70,58019%1BREWERY PLANTSANCHO/BBDO$ 180$ 32,094$ 31,91517744%1PEPSI COL+POSTOBONSANCHO/BBDO$ 3,856$ 20,385$ 16,529429%1SUCCESSFUL STORESSANCHO/BBDO$ 65,194$ 79,979$ 14,78523%1BANCOLOMBIASANCHO/BBDO$ 45,218$ 59,528$ 14,30932%1POLAR FOODSANCHO/BBDO$ 14,610$ 26,580$ 11,97082%1MERCADOLIBRE.COMSANCHO/BBDO$ 14,966$ 24,951$ 9,98667%1PEPSICO ALIMONYSANCHO/BBDO$ 11,078$ 17,517$ 6,43958%1ECOPETROLSANCHO/BBDO$ 8,340$ 12,604$ 4,26451%1LEADERS+CEETSANCHO/BBDO$ 2,234$ 5,461$ 3,226144%1C FUNCTIONAL DRINKSSANCHO/BBDO$ 965$ 3,937$ 2,972308%1DAIMLER COLOMBIA SASANCHO/BBDO$ 2,167$ 5,033$ 2,865132%1TERPELSANCHO/BBDO$ 1,763$ 4,196$ 2,433138%1AVON CALLINGSSANCHO/BBDO$ 268$ 1,493$ 1,225456%1ARTURO CALLESANCHO/BBDO$ 301$ 1,380$ 1,079359%1CORONASANCHO/BBDO$ 2,397$ 2,782$ 38516%1L&C S.A.SSANCHO/BBDO$ 892$ 1,238$ 34639%1PJ COL SASSANCHO/BBDO$ 478$ 812$ 33470%1ALM EXITO+B COLPATSANCHO/BBDO$ 83$ 230$ 147176%1COLGATE PALMOLIVEYOUNG & RUBICAM$ 159,954$ 257,348$ 97,39461%2COLGATE PALMOLIV INTYOUNG & RUBICAM$ 9,363$ 31,787$ 22,424240%2HUAWEI TECHNOLOGIESYOUNG & RUBICAM$ 541$ 3,313$ 2,772512%2DERCO SAYOUNG & RUBICAM$ 3,175$ 4,147$ 97231%2WALT DISNEY COMPANYBTL$ 145,513$ 254,319$ 108,80675%3But I get something like that AdvertiserAgency20232024DiffYoyRankxCHEMICAL TECHNOLOGIESSANCHO/BBDO$ 364,529$ 435,109$ 70,58019%1BREWERY PLANTSANCHO/BBDO$ 180$ 32,094$ 31,91517744%1PEPSI COL+POSTOBONSANCHO/BBDO$ 3,856$ 20,385$ 16,529429%1SUCCESSFUL STORESSANCHO/BBDO$ 65,194$ 79,979$ 14,78523%1BANCOLOMBIASANCHO/BBDO$ 45,218$ 59,528$ 14,30932%1POLAR FOODSANCHO/BBDO$ 14,610$ 26,580$ 11,97082%1MERCADOLIBRE.COMSANCHO/BBDO$ 14,966$ 24,951$ 9,98667%1PEPSICO ALIMONYSANCHO/BBDO$ 11,078$ 17,517$ 6,43958%1ECOPETROLSANCHO/BBDO$ 8,340$ 12,604$ 4,26451%1LEADERS+CEETSANCHO/BBDO$ 2,234$ 5,461$ 3,226144%1C FUNCTIONAL DRINKSSANCHO/BBDO$ 965$ 3,937$ 2,972308%1DAIMLER COLOMBIA SASANCHO/BBDO$ 2,167$ 5,033$ 2,865132%1TERPELSANCHO/BBDO$ 1,763$ 4,196$ 2,433138%1AVON CALLINGSSANCHO/BBDO$ 268$ 1,493$ 1,225456%1ARTURO CALLESANCHO/BBDO$ 301$ 1,380$ 1,079359%1CORONASANCHO/BBDO$ 2,397$ 2,782$ 38516%1L&C S.A.SSANCHO/BBDO$ 892$ 1,238$ 34639%1PJ COL SASSANCHO/BBDO$ 478$ 812$ 33470%1ALM EXITO+B COLPATSANCHO/BBDO$ 83$ 230$ 147176%1COLGATE PALMOLIVEYOUNG & RUBICAM$ 159,954$ 257,348$ 97,39461%1COLGATE PALMOLIV INTYOUNG & RUBICAM$ 9,363$ 31,787$ 22,424240%1HUAWEI TECHNOLOGIESYOUNG & RUBICAM$ 541$ 3,313$ 2,772512%1DERCO SAYOUNG & RUBICAM$ 3,175$ 4,147$ 97231%1WALT DISNEY COMPANYBTL$ 145,513$ 254,319$ 108,80675%1I have the following formulaRANKX Agency 1 Pos =VAR InvestmentYTD = [Net Investment YTD Agency]VAR VariationYTD = [YTD Variation Agency]RETURNIF(NOT ISBLANK(InversionYTD) && InversionYTD <> 0 && VariationYTD > 0,RANKX(FILTER(ALL('Industry'),[YTD Agency variation] > 0),CALCULATE YOURSELF[Net Investment YTD Agency],REMOVEFILTERS('Industry'), -- Remove filters from the advertiser columnREMOVEFILTERS('Industry') -- Also removes filters applied to the entire table if necessary),,DESCTurn),BLANK())

This was the solutionRANKX Agency 1 Pos =RANKX( FILTER( ADDCOLUMNS( SUMMARIZE( ALL(Industry[Agency], Industry[Advertiser]), -- Work at Agency and Advertiser level Industry[Agency], Industry[Advertiser] ), "VariationAgencyTotal", CALCULATE( [YTD Agency Variation], REMOVEFILTERS(Industry[Advertiser]) -- Evaluates variation at the Agency level ), "ExcludeAdvertiser", IF( CALCULATE( [YTD Agency Variation], REMOVEFILTERS(Industry[Advertiser]) ) > 0, FALSE(), -- Do not exclude if the Agency has a Positive Variation TRUE() -- Exclude if the Agency has Negative Variation ) ), [DeleteAdvertiser] = FALSE() -- Filter only Agency Advertisers with Positive Variation ), CALCULATE( [YTD Net Investment Agency], REMOVEFILTERS(Industry[Advertiser]) -- Ranking based on the Agency's total investment ), , DESC, DENSE)

Syndicate_Admin

Administrator

1 year ago

Solved

RAnK conditioning problem

I am trying to build a RANKX of Agencies, based on investment, but only of agencies that have positive variation, the issue is that everything gives me 1 since I am building a matrix that must be filtered by this rank to visualize the advertisers when the rank is number 1, the issue is that when I apply the filter everything gives 1 and does not bring me the correct advertisers

I expect a result like the following

Advertiser	Agency	2023	2024	Diff	Yoy	Rankx
CHEMICAL TECHNOLOGIES	SANCHO/BBDO	$ 364,529	$ 435,109	$ 70,580	19%	1
BREWERY PLANT	SANCHO/BBDO	$ 180	$ 32,094	$ 31,915	17744%	1
PEPSI COL+POSTOBON	SANCHO/BBDO	$ 3,856	$ 20,385	$ 16,529	429%	1
SUCCESSFUL STORES	SANCHO/BBDO	$ 65,194	$ 79,979	$ 14,785	23%	1
BANCOLOMBIA	SANCHO/BBDO	$ 45,218	$ 59,528	$ 14,309	32%	1
POLAR FOOD	SANCHO/BBDO	$ 14,610	$ 26,580	$ 11,970	82%	1
MERCADOLIBRE.COM	SANCHO/BBDO	$ 14,966	$ 24,951	$ 9,986	67%	1
PEPSICO ALIMONY	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 FUNCTIONAL DRINKS	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

But I get something like that

Advertiser	Agency	2023	2024	Diff	Yoy	Rankx
CHEMICAL TECHNOLOGIES	SANCHO/BBDO	$ 364,529	$ 435,109	$ 70,580	19%	1
BREWERY PLANT	SANCHO/BBDO	$ 180	$ 32,094	$ 31,915	17744%	1
PEPSI COL+POSTOBON	SANCHO/BBDO	$ 3,856	$ 20,385	$ 16,529	429%	1
SUCCESSFUL STORES	SANCHO/BBDO	$ 65,194	$ 79,979	$ 14,785	23%	1
BANCOLOMBIA	SANCHO/BBDO	$ 45,218	$ 59,528	$ 14,309	32%	1
POLAR FOOD	SANCHO/BBDO	$ 14,610	$ 26,580	$ 11,970	82%	1
MERCADOLIBRE.COM	SANCHO/BBDO	$ 14,966	$ 24,951	$ 9,986	67%	1
PEPSICO ALIMONY	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 FUNCTIONAL DRINKS	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%	1
COLGATE PALMOLIV INT	YOUNG & RUBICAM	$ 9,363	$ 31,787	$ 22,424	240%	1
HUAWEI TECHNOLOGIES	YOUNG & RUBICAM	$ 541	$ 3,313	$ 2,772	512%	1
DERCO SA	YOUNG & RUBICAM	$ 3,175	$ 4,147	$ 972	31%	1
WALT DISNEY COMPANY	BTL	$ 145,513	$ 254,319	$ 108,806	75%	1

I have the following formula
RANKX Agency 1 Pos =
VAR InvestmentYTD = [Net Investment YTD Agency]
VAR VariationYTD = [YTD Variation Agency]
RETURN
IF(
NOT ISBLANK(InversionYTD) && InversionYTD <> 0 && VariationYTD > 0,
RANKX(
FILTER(
ALL('Industry'),
[YTD Agency variation] > 0
),
CALCULATE YOURSELF
[Net Investment YTD Agency],
REMOVEFILTERS('Industry'), -- Remove filters from the advertiser column
REMOVEFILTERS('Industry') -- Also removes filters applied to the entire table if necessary
),
,
DESC
Turn
),
BLANK()
)

Syndicate_Admin
1 year ago
This was the solution

RANKX Agency 1 Pos =
RANKX(
FILTER(
ADDCOLUMNS(
SUMMARIZE(
ALL(Industry[Agency], Industry[Advertiser]), -- Work at Agency and Advertiser level
Industry[Agency],
Industry[Advertiser]
),
"VariationAgencyTotal",
CALCULATE(
[YTD Agency Variation],
REMOVEFILTERS(Industry[Advertiser]) -- Evaluates variation at the Agency level
),
"ExcludeAdvertiser",
IF(
CALCULATE(
[YTD Agency Variation],
REMOVEFILTERS(Industry[Advertiser])
) > 0,
FALSE(), -- Do not exclude if the Agency has a Positive Variation
TRUE() -- Exclude if the Agency has Negative Variation
)
),
[DeleteAdvertiser] = FALSE() -- Filter only Agency Advertisers with Positive Variation
),
CALCULATE(
[YTD Net Investment Agency],
REMOVEFILTERS(Industry[Advertiser]) -- Ranking based on the Agency's total investment
),
,
DESC,
DENSE
)

4 Replies

amitchandak
Super User
1 year ago
Syndicate_Admin , Please create measure seperatly in case you want remove filter and get subtotal

and then on that measure have rank like

Rankx(allselected(Table[Advertiser], Table[Agency]), [Measure])

in case you have columns across table, use summarize, exmple

Rank across dimension tables: https://youtu.be/X59qp5gfQoA

Consider new function Rank, if needed

Power BI - New DAX Function: RANK - How It Differs from RANKX: https://youtu.be/TjGkF44VtDo
- Syndicate_Admin
  Administrator
  1 year ago
  Okay, I got this formula that gives me the right result
  
  RANKX Agency 1 Pos =
  RANKX(
  SUMMARIZE(
  ALLSELECTED(Industria),
  Industry[Advertiser],
  Industry[Agency]),
  CALCULATE([Net Investment YTD Agency],
  REMOVEFILTERS(Industry[Advertiser]))
  ,
  ,
  DESC,
  Dense
  )
  
  Now I need to exclude Agencies with Negative YoY, they should not appear in the ranking, and I try something like
  
  RANKX Agency 1 Pos =
  RANKX(
  FILTER(
  SUMMARIZE(
  ALLSELECTED(Industria),
  Industry[Advertiser],
  Industry[Agency],
  "InvestmentTotal", CALCULATE([Net Investment YTD Agency], REMOVEFILTERS(Industry[Advertiser])),
  "VariacionTotal", CALCULATE([Variation YTD Agency], REMOVEFILTERS(Industry[Advertiser]))
  ),
  [TotalVariation] > 0 -- Includes only agencies with positive variance
  ),
  CALCULATE([Net Investment YTD Agency], REMOVEFILTERS(Industry[Advertiser])),
  ,
  DESC,
  DENSE
  )
  
  But the result is blank
Syndicate_Admin
Administrator
1 year ago
Okay, I got this formula that gives me the right result

RANKX Agency 1 Pos =
RANKX(
SUMMARIZE(
ALLSELECTED(Industry),
Industry[Advertiser],
Industry[Agency]),
CALCULATE([Net Investment YTD Agency],
REMOVEFILTERS(Industry[Advertiser]))
,
,
DESC
Turn
)

Now I need to exclude Agencies with Negative YoY, they should not appear in the ranking, and I try something like

RANKX Agency 1 Pos =
RANKX(
FILTER(
SUMMARIZE(
ALLSELECTED(Industry),
Industry[Advertiser],
Industry[Agency],
"InvestmentTotal", CALCULATE([Net Investment YTD Agency], REMOVEFILTERS(Industry[Advertiser])),
"VariacionTotal", CALCULATE([Variation YTD Agency], REMOVEFILTERS(Industry[Advertiser]))
),
[TotalVariation] > 0 -- Includes only agencies with positive variance
),
CALCULATE([Net Investment YTD Agency], REMOVEFILTERS(Industry[Advertiser])),
,
DESC
TURN
)

But the result is blank
Syndicate_Admin
Administrator
1 year ago
This was the solution

RANKX Agency 1 Pos =
RANKX(
FILTER(
ADDCOLUMNS(
SUMMARIZE(
ALL(Industry[Agency], Industry[Advertiser]), -- Work at Agency and Advertiser level
Industry[Agency],
Industry[Advertiser]
),
"VariationAgencyTotal",
CALCULATE(
[YTD Agency Variation],
REMOVEFILTERS(Industry[Advertiser]) -- Evaluates variation at the Agency level
),
"ExcludeAdvertiser",
IF(
CALCULATE(
[YTD Agency Variation],
REMOVEFILTERS(Industry[Advertiser])
) > 0,
FALSE(), -- Do not exclude if the Agency has a Positive Variation
TRUE() -- Exclude if the Agency has Negative Variation
)
),
[DeleteAdvertiser] = FALSE() -- Filter only Agency Advertisers with Positive Variation
),
CALCULATE(
[YTD Net Investment Agency],
REMOVEFILTERS(Industry[Advertiser]) -- Ranking based on the Agency's total investment
),
,
DESC,
DENSE
)

Forum Discussion

RAnK conditioning problem

4 Replies

Related content

Rank with conditions

Duplicate ranking

Ranking KPI card

Dynamic Ranking

RANK indexing with condition