Ranking based on score average and slicer selection

[Jatin77]

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)

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

2)

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 ID	name	Marks	x1_name	x2_name	x3_name	x4_name	Available/Unavailable	Rank (o/p - column)
1	A		DP1	KJ1	IC4	YT4	Available
1	A	47	DP2	KJ5	IC4	YT5	Available
1	A	12	DP3	KJ3	IC1	YT1	Available
1	A		DP4	KJ5	IC2	YT2	Available
2	B		DP1	KJ3	IC3	YT3	Available
2	B		DP2	KJ4	IC4	YT4	Available
2	B		DP3	KJ4	IC5	YT1	Available
2	B	19	DP4	KJ2	IC4	YT2	Available
2	B	45	DP5	KJ3	IC2	YT3	Available
2	B	31	DP4	KJ4	IC5	YT4	Available
3	C	42	DP2	KJ5	IC3	YT5	Unavailable
3	C		DP3	KJ1	IC4	YT4	Unavailable
3	C		DP4	KJ2	IC4	YT3	Unavailable
4	D	28	DP1	KJ3	IC2	YT4	Unavailable
5	E	49	DP5	KJ4	IC3	YT5	Unavailable
6	F	25	DP3	KJ1	IC4	YT5	Available
6	F		DP4	KJ2	IC5	YT3	Available
7	G		DP4	KJ3	IC1	YT5	Available
7	G		DP2	KJ4	IC2	YT3	Available
7	G		DP3	KJ5	IC3	YT4	Available
7	G		DP4	KJ4	IC4	YT4	Available
7	G	9	DP1	KJ2	IC1	YT2	Available
8	H	30	DP5	KJ3	IC2	YT3	Available
8	H	16	DP3	KJ4	IC3	YT4	Available
8	H	43	DP5	KJ4	IC4	YT5	Available
8	H	35	DP3	KJ5	IC5	YT1	Available
8	H	14	DP4	KJ1	IC4	YT2	Available
9	I		DP4	KJ2	IC3	YT3	Unavailable
9	I		DP2	KJ3	IC4	YT4	Unavailable
9	I		DP3	KJ4	IC5	YT1	Unavailable
9	I	28	DP4	KJ1	IC5	YT2	Unavailable
9	I	46	DP5	KJ2	IC3	YT3	Unavailable

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

[amitchandak · Accepted solution]

@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

[Jatin77]

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