Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
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 fil...
  • Syndicate_Admin's avatar
    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
    )
Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
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