Dynamic ranking for groups

[rishirajdeb]

Hi All,

Need help with dynamic ranking for a group (customer profile). I have gone through the community posts for similar topics, however not found an identical scenario yet.

Following is some quick mock up data for the fact table that I have (may not be a good representation of the actual data but should serve the purpose of explaining the requirement 😊)

Let's say I have a date dimension in the model which has been used for the date slicers on the report. Based on the above data, the output I am looking for is as below:

Slicer selection: Jan-2023

Slicer selection: Feb-2023

So, need to create those customer profiles like "Top 1-3" (rank 1, 2 and 3), "Top 4-6" etc. and then display the total amount for the profiles. Initially I created a calculated table grouped by customers and then created the customer profile but soon realized that would not be dynamic with different slicer selections as, for example, the "Top 1-3" profile would not have the same 3 customers for each month.

The only way I see is to create a standalone table with all possible distinct profiles, then create a measure - however not been able to make it work yet.

Please can someone help?

@amitchandak @lbendlin @Greg_Deckler @Data-Rainer @tamerj1 @ppm1 @cassidy 

[tamerj1 · Accepted solution]

Hi @rishirajdeb 

manually created a single column disconnect table that contains the three profiles. Place profiles[Profile] in a table visual along with the following measure 

ResultMeasure =
VAR SelecetedTable =
SUMMARIZE (
ALLSELECTED ( 'Table' ),
'Table'[Customer],
"@Amont", SUM ( 'Table'[Amount] )
)
VAR Top3 =
TOPN ( 3, SelecetedTable, [@Amont] )
VAR Top6 =
TOPN ( 6, SelecetedTable, [@Amount] )
RETURN
SUMX (
VALUES ( Profiles[Profile] ),
SWITCH (
Profiles[Profile],
"Top1-3", SUMX ( Top3, [@Amont] ),
"Top4-6", SUMX ( EXCEPT ( Top6, Top3 ), [@Amount] ),
SUMX ( EXCEPT ( SelectedTable, Top6 ), [@Amount] )
)
)

[rishirajdeb]

That's exactly what I wanted. Just modified the return statement a bit as per my need and used Switch(Selectedvalue(Profiles[Profile])) instead (not really want to show the summation in my Total). Thanks a lot!!!