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

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