Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
rishirajdeb
Advocate I
Advocate I

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

 

rishirajdeb_0-1683658686368.png

 

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

 

rishirajdeb_1-1683658718958.png

 

Slicer selection: Feb-2023

 

rishirajdeb_2-1683658743881.png

 

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 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.