RANX duplicate rank values

[RoxFreyja191]

Hi All,

I am new to Power Bi (month 1).

I am trying to rank client spend. The table with the value of each purchased line does not contain the client name, but it is connected with a relationship on the Order ID. 

Here is what I have done:

Ranking = RANKX(
CROSSJOIN(
ALL(Organizations[Organisation Name Short]),ALL(SalesOrderLines[omlExtendedDiscountBase])
),
[Total Sales Orders]
)

The results almost work, except that there are a few duplicate ranks even though the sales value is not the same.

Note rank 3 is used twice.

I would appreciate any advice

[v-robertq-msft · Accepted solution]

Hi, 

According to your description, I can roughly understand your requirement, I think you can try to create a new measure using the Rand() function to assist your original sorting in the measure, you can try my steps:

Create a new measure:

Randome = RAND()

Then change the measure Ranking like this:

Ranking =

RANKX(
CROSSJOIN(
ALL(Organizations[Organisation Name Short]),ALL(SalesOrderLines[omlExtendedDiscountBase])
),
[Total Sales Orders] + [Randome]
)

And you can get what you want, like this:

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RoxFreyja191]

Sorry need to clarify. 

The calculation of values takes place in Table A (No Org names) it connects with a sales order ID to Table B which has a short version of the customer ID which connect to table C (Organisations) that has the category name I want to use.

So the relationship is not directly between table A (sales lines) and C (Organisations), its connected through a table B.