Maintain high level ranking when drilling down

[Anonymous]

Hi.

I am trying to drill down a chart that was built based on Top N personnel with overtime. So I have a 'Select N' filter and I also have a filter on date. I would like to show top N personnel with the most overtime for 3 months cumulative. So I'll select 3 months from the date filter. I have the following measures:

Rank by OT =

VAR IsEmployeeFiltered = ISFILTERED('Monthly Data'[Personnel])

VAR IsMonthFiltered = ISFILTERED('Monthly Data'[Cal. Year/Month])

RETURN

IF (IsEmployeeFiltered && IsMonthFiltered,

RANKX(ALL('Monthly Data'[Personnel]), CALCULATE(SUM('Monthly Data'[Overtime Hours]))),

IF (NOT IsEmployeeFiltered && IsMonthFiltered,

RANKX(ALL('Monthly Data'[Personnel]), CALCULATE(SUM('Monthly Data'[Overtime Hours]))),

BLANK()

)

)

I have another measure which I filter for "True" so that my table or graph just shows the Top N:

Employee Rank within Selected N = IF([Rank by OT] <= [Selected N],"True","False")

This is all fine if I visualise by personnel and total overtime only but I would like to add the months to the chart. So I'd like to see the top 5 personnel with highest overtime for December, January and February in columns. When I drill down with my current measures personnel not a part of the top 5 are included because in one of the selected months their ranking was equal to or less than '5'. But I need the ranking to remain on the total for the 3 months. 

I am hoping I can fix this line in the first measure seeing that the Personnel column in the table is not actually filtered. How can I some how filter the Personnel column based on the filtered measure where Rank By OT <= Selected N? Is there an alternative to ISfilter for measures?

VAR IsEmployeeFiltered = ISFILTERED('Monthly Data'[Personnel])

[Anonymous · Accepted solution]

After many hours of research I was able to find a simple solution to my problem.

Measure 1

Total OT = CALCULATE( SUM( 'Data'[Hours]), ALLSELECTED('Data'[Date]))

Measure 2

Rank OT =     
RANKX (
        ALLSELECTED ('Data'[Name]),
    CALCULATE (
        [Total OT]
    ),
    ,
    DESC,
    Dense
)

Measure 3

Employee Rank = IF [Rank OT] <= [Selected N], "True", "False")

[v-cazheng-msft]

Hi @Anonymous 

Can you provide some sample removing sensitive data?

Best Regards,

Caiyun Zheng

[Anonymous]

hi, suppose I have three columns: Name, Date and overtime and I wanted the top 3 persons for April, May and June cumulatively. The pivot table shows the ranking I would expect to see even if I drill down to see the trend of the 3 months.

Name Month/Year Overtime
John 1/1/2020 70
John 2/1/2020 35
John 3/1/2020 42
John 4/1/2020 61
John 5/1/2020 53
John 6/1/2020 13
David 1/1/2020 73
David 2/1/2020 45
David 3/1/2020 66
David 4/1/2020 13
David 5/1/2020 2
David 6/1/2020 1
Samantha 1/1/2020 10
Samantha 2/1/2020 48
Samantha 3/1/2020 17
Samantha 4/1/2020 4
Samantha 5/1/2020 3
Samantha 6/1/2020 49
Rose 1/1/2020 5
Rose 2/1/2020 2
Rose 3/1/2020 8
Rose 4/1/2020 5
Rose 5/1/2020 4
Rose 6/1/2020 10
Mark 1/1/2020 14
Mark 2/1/2020 6
Mark 3/1/2020 6
Mark 4/1/2020 20
Mark 5/1/2020 10
Mark 6/1/2020 6