Distribute a value unevenly across different rows based on rank

[kkalyanrr]

Hello Team,

I'm trying to achieve the below expected result,Please guide.
I need to distribute X column across all the Names un evenly, least rank gets highest value and highest rank gets lowest value.

Name	Overall	X	Rank(Based on Overall)	Z
A	15	55	1	22
B	18	55	2	15
C	20	55	3	10
D	22	55	4	5
E	25	55	5	2
F	28	55	6	1

Thanks.

[Icey · Accepted solution]

Hi @kkalyanrr ,

How about this?

1. Create a calculated table.

Rand Table = 
VAR RowNum_ =
    COUNTROWS ( 'Table' )
RETURN
    ADDCOLUMNS ( GENERATESERIES ( 1, RowNum_, 1 ), "Rand", RAND () )

2. Add "Modified Rand" and "Rank" column in the "Rand Table".

Modified Rand = 
MAX ( 'Table'[X] ) / SUM ( 'Rand Table'[Rand] ) * [Rand]

Rank =
RANKX ( 'Rand Table', [Modified Rand],, DESC, DENSE )

3. Create "Modified Rand" column in your table.

Modified Rand = 
LOOKUPVALUE (
    'Rand Table'[Modified Rand],
    'Rand Table'[Rank], 'Table'[Rank(Based on Overall)]
)

4. Hide the "Rand Table".

5. You can find that the total is 55 and the Modified Rand column is sorted.

Best Regards,

Icey

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

[kkalyanrr]

@selimovd - Thanks for the quick response.

these 22,15 are few random numbers..basically that 55 should be distributed across the no of rows (in this case 6 rows..it may be 10 rows or 12 rows)...
the highest rank should get least number and the lowest get highst number..thats the logic I'm expecting.

[selimovd]

Hello @kkalyanrr ,

can you give more information about the mechanism how the points are distributed?

Does the first always get 22? Is it always 22, 15, ... 1? What if there are 7 or 8 instead of 6 rows? Give some more information and we can help you.

Best regards

Denis

[kkalyanrr]

@selimovd 

Its just random sampling...the only point is the highest rank should get lowest value and lowest rank should get highest value..