How to create a dynamic decile ranking

[AABright]

I am attempting to create a decile ranking which will change depending on selections.

I have a Dim table like this:

Dim Table
ID	Group	Name	Number
AV1	A	V	1
AW2	A	W	2
AX2	A	X	2
AY2	A	Y	2
AZ2	A	Z	2
BW1	B	W	1
BX2	B	X	2
BY3	B	Y	3

My Fact table is joined to the Dim on the ID field. So, for example, if selections are made where Group = A and Number = 2, my Fact table would result in this dataset, with the corresponding Score values.

Fact Table
ID	Score
AW2	4.5
AX2	2.1
AY2	2.1
AZ2	1

What I need to do is arrive at a decile ranking of those IDs, across their Scores.

Step 1 would be to rank the values (There will be duplicate scores, so they can have a secondary orderby which doesn't matter. And there will be 0 and blank values).

Step 2 is to get the rate, dividing the rank be the count of all selected IDs.

Step 3 is to bucket the rates into deciles.

Fact Table			Calculations
ID	Score		Rank	Rate (rank/count ID)	Decile
AW2	4.5		4	1	1
AX2	2.1		3	0.75	3
AY2	2.1		2	0.5	5
AZ2	1		1	0.25	8

I have been researching this for a couple days now and am stuck at square one.

Thanks for your help!

[AABright]

Thanks @Greg_Deckler. That second measure did create a ranking which handled the dupes. 

I didn't describe the fact table fully. The grain is at an individual (member) level which each are connected to a group ID. So when the IDs are selected from Dim_Table, Fact_Table will have multiplied rows of members belonging to those groups. And the members need to be ranked/bucketed into deciles.

Fact Table				Calculations
ID	Member	Score		Rank	Rate (rank/count ID)	Decile
AW2	G434F	7.1		1	0.125
AW2	W45MW	5		2	0.25
AX2	VKE0K	4.2		4	0.5
AX2	DLKD00	4.2		5	0.625
AX2	F9FN3	4.7		3	0.375
AX2	6WSLF			8	1
AY2	02LFJW0	2		6	0.75
AZ2	KOS02	0		7	0.875

The second measure you suggested results in this table, starting at 104K rather than 1.

[Greg_Deckler · Accepted solution]

@AABright So, probably something like this. PBIX is attached below signature.

Rank2 = RANK( SKIP, ALLSELECTED('Table2'), ORDERBY( CALCULATE(SUM('Table2'[Score])), DESC, CALCULATE(MAX('Table2'[Member])), DESC) )

or

Rank3 = 
    VAR __ID = MAX('Table2'[Member])
    VAR __Count = COUNTROWS( ALLSELECTED('Table2') )
    VAR __Text = CONCATENATEX( ALLSELECTED('Table2'), [Member] & "^" & [Score], "|", [Score], DESC, [Member], DESC)
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS( 
                GENERATESERIES( 1, __Count, 1 ),
                "__Value", SUBSTITUTE( PATHITEM( __Text, [Value] ), "^", "|" )
            ),
            "__ID", PATHITEM( [__Value], 1 ),
            "__Score", PATHITEM( [__Value], 2 )
        )
    VAR __Result = MAXX( FILTER( __Table, [__ID] = __ID ), [Value] )
RETURN
    __Result

[Greg_Deckler]

@AABright Well, the first issue you are going to have is that neither Dense nor Skip will provide you with a truly unique Rank. Both will assign the same number to the duplicate values. So, to get around this you have to get a bit fancy and use what is effectively known as the Mythical DAX Index: The Mythical DAX Index Quick Measure - Microsoft Fabric Community

Here is an example that will return a unique rank for each ID. Note that this index is generated by first sorting by score and then by ID so AX2 will be sorted above AY2 even though they have the same score. I am assuming that you can generate the rest of the DAX for your decile.

Rank = 
    VAR __ID = MAX('Table'[ID])
    VAR __Count = COUNTROWS( ALLSELECTED('Table') )
    VAR __Text = CONCATENATEX( ALLSELECTED('Table'), [ID] & "^" & [Score], "|", [Score], ASC, [ID], DESC)
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS( 
                GENERATESERIES( 1, 4, 1 ),
                "__Value", SUBSTITUTE( PATHITEM( __Text, [Value] ), "^", "|" )
            ),
            "__ID", PATHITEM( [__Value], 1 ),
            "__Score", PATHITEM( [__Value], 2 )
        )
    VAR __Result = MAXX( FILTER( __Table, [__ID] = __ID ), [Value] )
RETURN
    __Result

Now, the above is verbose but it is clear what is going on and how the calculation actually works. Otherwise, if you enjoy black box magical DAX then this will also return a unique ranking with the same sorting:

Rank 2 = RANK( SKIP, ALLSELECTED('Table'), ORDERBY( CALCULATE(SUM('Table'[Score])), ASC, CALCULATE(MAX('Table'[ID])), DESC) )

Both of these are measures FYI.