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

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
)