topic Re: Issue with RankX in a Matrix - Duplicate Ranks for Differing Values in DAX Commands and Tips

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

hoyt22 — Sat, 26 Oct 2024 00:41:10 GMT

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

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

lbendlin — Fri, 25 Oct 2024 22:33:59 GMT

link requires access.

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

hoyt22 — Sat, 26 Oct 2024 00:45:02 GMT

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

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

lbendlin — Sat, 26 Oct 2024 02:05:35 GMT

see attached

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

hoyt22 — Sat, 26 Oct 2024 02:39:20 GMT

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!

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

lbendlin — Sat, 26 Oct 2024 13:15:15 GMT

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

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

hoyt22 — Sat, 26 Oct 2024 21:46:39 GMT

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!

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

lbendlin — Sat, 26 Oct 2024 22:40:08 GMT

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.

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

ThxAlot — Sun, 27 Oct 2024 22:43:45 GMT