Forum Discussion

rishirajdeb's avatar
rishirajdeb
Advocate I
3 years ago
Solved

Dynamic ranking for groups

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 

Need Help
  • tamerj1's avatar
    tamerj1
    3 years ago

    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] )
    )
    )

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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's avatar
      rishirajdeb
      Advocate I
      3 years ago

      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!!!