cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rsbin
Post Partisan
Post Partisan

Dynamic Top N: Facility / Employee / Overtime Hours

Good Afternoon All,

I am creating a dynamic Top N report to capture Overtime Hours by Employee at different facilities.

I have the following Slicers:  PayWeek, Facility, and my Top N Selector.

rsbin_1-1601493429745.png

 

I am not sure how to proceed at this point.  Based on the Slicer Selections, I need to populate a bar chart.

Daily data is stored in a Fact table called OTStats. 

For each Employee, I will need to Sum OT Hours for each day of the PayWeek selected (multiple weeks are allowed).

 

rsbin_0-1601492834067.png

I think I need to create some sort of table (virtual or actual), but am stuck.  Any guidance would be very much appreciated.

1 ACCEPTED SOLUTION
jdbuchanan71
Super User II
Super User II

@rsbin 

I am assuming you have a measure to sum the OT Hours, in my example that is [OT Hours].  Then you would use something like this:

Top N OT Employees = 
VAR _SelectedN =
    SELECTEDVALUE ( 'Select Top N'[Top N Value], 10 )
RETURN
    CALCULATE (
        [OT Hours],
        KEEPFILTERS( TOPN ( _SelectedN, ALL ( 'YourTable'[Employee Name] ), [OT Hours] ) )
    )

View solution in original post

2 REPLIES 2
jdbuchanan71
Super User II
Super User II

@rsbin 

I am assuming you have a measure to sum the OT Hours, in my example that is [OT Hours].  Then you would use something like this:

Top N OT Employees = 
VAR _SelectedN =
    SELECTEDVALUE ( 'Select Top N'[Top N Value], 10 )
RETURN
    CALCULATE (
        [OT Hours],
        KEEPFILTERS( TOPN ( _SelectedN, ALL ( 'YourTable'[Employee Name] ), [OT Hours] ) )
    )

View solution in original post

Hello @jdbuchanan71 ,

 

Thank You for the quick response!

Pretty sure that looks like the answer I was looking for!

 

Thanks again and of course Kudos to you.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors