Issue with RankX in a Matrix - Duplicate Ranks for Differing Values

[hoyt22]

Hi all,

I have a matrix with Market and Advertiser in the rows, and I want to rank the Advertisers within each Market on different KPIs. This seems straightforward, but I'm getting duplicate ranks for advertisers even when their values are different. For example:

I have TotalSpend:

TotalSpend = SUM(Data[$])

And Rank_Spend:

Rank_Spend = 
IF(
    [TotalSpend] > 0,
    RANKX(
        ALL('Data'[ADVERTISER]),
        [TotalSpend],,DESC, Dense)
)

But, as you can see, the ranks are incorrect.

I want the user to be able to select which advertisers are displayed in a market, but not have the ranks change. For example, if the user chooses to only show Coolray Cooling Heating Plumbing, the rank in Atlanta will still show as 4.

I feel like I'm missing something obvious, but any help would be appreciated. I have my sample dashboard and dataset below. Thanks!

Sample Dashboard and Dataset

[lbendlin]

link requires access.

[hoyt22]

@lbendlin Apologies! I've fixed the link. Thank you!

[lbendlin]

see attached

[hoyt22]

@lbendlin The only issue I see is that I don't want the rank of the advertiser to change based on the selections in the Advertiser slicer. As you have it, if I only have one advertiser in the market selected, its rank will update to 1. But I'd like it to stay whatever it was as if all advertisers are still included. Does that make sense? I tried changing ALLSELECTED to ALL, but it messed up the ranks.

Thanks for your help!

[lbendlin]

Sounds like you want a calculated column instead? Or do you still want the time filter to be observed?

[hoyt22]

@lbendlin I still want the date slicer to be observed. The Advertiser slicer should just affect what shows in the matrix but not the rankings themselves. Let me know if that isn't clear. I appreciate your help!

[ThxAlot · Accepted solution]

[lbendlin]

Then you will have to invest in a proper data model, with a calendar table, a Market table, and a Advertisers table.  Not possible to do this all in the same fact table.