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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jatin77
Helper I
Helper I

Ranking based on score average and slicer selection

I have following data table and I want to write a dax for a measure such that I want to show the top 5 empid based on the average of marks, there are multiple entries for the same emp id as you can see in the data table, and I have multiple slicers and if any selection is done in any of the slicer then the table should show ranking as per the slicer selection and if nothing is selected in the slicer then the table should show overall ranking based on average of mapped block score and ranking should not consider "unavailable" emp id.

For Ex.
1)

Jatin77_0-1674129640800.png


Now in above scenario nothing is selected in any of the slicer, now the ranking should be based on overall average of score.

2)

Jatin77_1-1674129695323.png



Now in this condition, “DP2” is selected in a slicer X1_name, now the average of marks should get calculated for DP2 for that emp id and then the ranking should show based on that calculation.

Similar kind of ranking should appear as per respective selection in any of the slicer.

Below is the data table structure.

emp IDnameMarksx1_namex2_namex3_namex4_nameAvailable/UnavailableRank (o/p - column)
1A DP1KJ1IC4YT4Available 
1A47DP2KJ5IC4YT5Available 
1A12DP3KJ3IC1YT1Available 
1A DP4KJ5IC2YT2Available 
2B DP1KJ3IC3YT3Available 
2B DP2KJ4IC4YT4Available 
2B DP3KJ4IC5YT1Available 
2B19DP4KJ2IC4YT2Available 
2B45DP5KJ3IC2YT3Available 
2B31DP4KJ4IC5YT4Available 
3C42DP2KJ5IC3YT5Unavailable 
3C DP3KJ1IC4YT4Unavailable 
3C DP4KJ2IC4YT3Unavailable 
4D28DP1KJ3IC2YT4Unavailable 
5E49DP5KJ4IC3YT5Unavailable 
6F25DP3KJ1IC4YT5Available 
6F DP4KJ2IC5YT3Available 
7G DP4KJ3IC1YT5Available 
7G DP2KJ4IC2YT3Available 
7G DP3KJ5IC3YT4Available 
7G DP4KJ4IC4YT4Available 
7G9DP1KJ2IC1YT2Available 
8H30DP5KJ3IC2YT3Available 
8H16DP3KJ4IC3YT4Available 
8H43DP5KJ4IC4YT5Available 
8H35DP3KJ5IC5YT1Available 
8H14DP4KJ1IC4YT2Available 
9I DP4KJ2IC3YT3Unavailable 
9I DP2KJ3IC4YT4Unavailable 
9I DP3KJ4IC5YT1Unavailable 
9I28DP4KJ1IC5YT2Unavailable 
9I46DP5KJ2IC3YT3Unavailable 


Below is the Dax I have tried:

rank =

var overall = RANKX(ALL('Sheet1 (2)'), CALCULATE(AVERAGE('Sheet1 (2)'[Marks]), ALLEXCEPT('Sheet1 (2)','Sheet1 (2)'[emp ID]) && 'Sheet1 (2)'[Available/Unavailable]<>"Unavailable"),,DESC,Dense)

var v1 = RANKX(ALL('Sheet1 (2)'), CALCULATE(AVERAGE('Sheet1 (2)'[Marks]), ALLEXCEPT('Sheet1 (2)','Sheet1 (2)'[emp ID], 'Sheet1 (2)'[x1_name]) && 'Sheet1 (2)'[Available/Unavailable]<>"Unavailable"),,DESC,Dense)

var v2 = RANKX(ALL('Sheet1 (2)'), CALCULATE(AVERAGE('Sheet1 (2)'[Marks]), ALLEXCEPT('Sheet1 (2)','Sheet1 (2)'[emp ID], 'Sheet1 (2)'[x2_name]) && 'Sheet1 (2)'[Available/Unavailable]<>"Unavailable"),,DESC,Dense)

var v3 = RANKX(ALL('Sheet1 (2)'), CALCULATE(AVERAGE('Sheet1 (2)'[Marks]), ALLEXCEPT('Sheet1 (2)','Sheet1 (2)'[emp ID],'Sheet1 (2)'[x3_name]) && 'Sheet1 (2)'[Available/Unavailable]<>"Unavailable"),,DESC,Dense)

var v4 = RANKX(ALL('Sheet1 (2)'), CALCULATE(AVERAGE('Sheet1 (2)'[Marks]), ALLEXCEPT('Sheet1 (2)','Sheet1 (2)'[emp ID], 'Sheet1 (2)'[x4_name]) && 'Sheet1 (2)'[Available/Unavailable]<>"Unavailable"),,DESC,Dense)


return

IF(
    ISFILTERED('Sheet1 (2)'[x1_name]), v1,
    IF(
        ISFILTERED('Sheet1 (2)'[x2_name]),v2,
        IF(
            ISFILTERED('Sheet1 (2)'[x3_name]), v3,
            IF(
                ISFILTERED('Sheet1 (2)'[x4_name]), v4,
                overall
            )
        )
    )
)

Regards,
Jatin77
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Jatin77 , I think you need use allselected, in case you want filter to work. Also prefer isinscope. refer my example

IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Jatin77 , I think you need use allselected, in case you want filter to work. Also prefer isinscope. refer my example

IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM

Hi @amitchandak , Thank you for replying. I have used "all selected" in place of "all" and this resolved the issue. Thanks a lot!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors