topic Re: RANKX skipping number even when values are different in DAX Commands and Tips

RANKX skipping number even when values are different

Anonymous — Fri, 16 Aug 2024 11:25:40 GMT

I have an issue where I've created a RANKX measure that at seemingly random skips a number/rank even though the measure/value that it's ranking on is different.

The base value/measure:

Avg A= AVERAGE(v_Sales[A]) / 1000

Rank measure:

Rank_ValueA = RANKX( FILTER( ALLEXCEPT('v_Stores', dimMarket[Market]), [Avg A] > 0 ), [Avg A], , , Dense )

Since I have different markets I want to rank within that market, which is why I have the "ALLEXCEPT"

Example of where it goes wrong, 47 is missing (first column is ID)

Another example, 60 is missing

On the table I have a filter for that market and a datefilet

Re: RANKX skipping number even when values are different

lbendlin — Fri, 16 Aug 2024 12:24:18 GMT

What is

ALLEXCEPT('v_Stores', dimMarket[Market])

supposed to accomplish?

Re: RANKX skipping number even when values are different

Anonymous — Fri, 16 Aug 2024 12:42:03 GMT

lbendlin

So that it ranks per market and not the total, at least that's the plan

Re: RANKX skipping number even when values are different

lbendlin — Fri, 16 Aug 2024 12:42:31 GMT

You'd need to summarize rather than filter.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: RANKX skipping number even when values are different

Anonymous — Fri, 16 Aug 2024 14:26:42 GMT

Here's an excel I hope is adequate

https://www.transfernow.net/dl/20240816h0BBgLOA

Thanks

Re: RANKX skipping number even when values are different

lbendlin — Fri, 16 Aug 2024 16:48:02 GMT

That Excel file only has Store No. and Value columns. Seems to be missing the Market column?

Re: RANKX skipping number even when values are different

Anonymous — Mon, 19 Aug 2024 07:21:53 GMT

Oh, yeah for this example they're all in the same market. Since I want to have separate matrixes for all markets and rank per market

Re: RANKX skipping number even when values are different

Anonymous — Mon, 19 Aug 2024 11:53:32 GMT

Update, I'm stupid, realised there was a global filter that affected the ranking, which I didn't think would affect...

So incorportated that into the rank and now it's correct