Forum Discussion
Solved
How to create a dynamic decile ranking
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 ...
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
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
Community Champion
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