Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Maintain high level ranking when drilling down

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])
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

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

3 Replies

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous 

    Can you provide some sample removing sensitive data?

     

    Best Regards,

    Caiyun Zheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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