CALCULATETABLE with SUMMARIZE and KEEPFILTERS

[ConnieMaldonado]

Hello:
I have a table of jobs logged by technicians (techs) in the field. Each row represents a job logged by the technician and includes the number of hours logged for the job. The data is structured by date, tech, and there are multiple entries for each date for each tech. Each tech has a location.

Sample data:

DATE	Employee_Number	Location	Tech Name	Hours
01/01/21	12345	Hickory	John Doe	6.3
01/01/21	12345	Hickory	John Doe	6.5
01/01/21	23456	Dallas	Jane Smith	7.8
01/01/21	23456	Dallas	Jane Smith	8
01/01/21	23456	Dallas	Jane Smith	2.9

I have a date slicer.

I need to include in a table the top techs by location based on the number of hours logged for the dates in the slicer.

For example, for Dallas there are 100 unique techs for the date period 1/1/21 through 6/11/21.

I need to rank the techs by hours logged and show the top 75 (75% of 100). I need to do the same for all locations.  In other words, I want to show 75% of technicians for each location, ranked based on their working hours between selected dates.

I want to create a logical variable called INCLUDE to indicate whether to include a tech in the table.

I created a sample PBIX and was able to get the RANKX function to work by creating a table using CALCULATETABLE and summarizing based on ID and LOC.  I used KEEPFILTERS to retain the date slicer, but it's not working.  See the table "Techs and Loc Table" in the PBIX in the following Google drive.  I need the table on the left to update based on the slicer. 

https://drive.google.com/drive/folders/1n0OyvFrSmnEhpKdMKYSEHhT7qukVf_Ct?usp=sharing

The data is dummy data, but represents the data I'm working with.  I have a date on the "Rank Calculaltions" table but am actually using a DATE table in my actual file.  I want to get this POC working and can then apply it to my actual file.

Calculating and storing the number of distinct techs (ID) by location and dates is another step, but I should be able to figure that out.  I think the harder logic is above.

[ConnieMaldonado]

Wow!  I don't know how you did that so fast.  I've been working on this for 2 days. 🙂  I have to make a tweak to include records with blank hours (count them as zero).  Nonperformers with no hours get excluded.  There was one record that I marked to exclude, and you marked to include (record had blank hours).  I'll see if I can fix that.

Thank you so much for your help; I really appreciate it.  You are awesome!

[DataZoe]

Thank you @ConnieMaldonado! I actually used a modification of a RANKX that I played around with a lot previously (Top/Bottom X On Same Visual Chart (datazoepowerbi.com)), so that helped me when I saw what you were trying to do :).  I am glad that it worked out!

[DataZoe · Accepted solution]

@ConnieMaldonado Please see the attached PBIX, which I think does what you are looking for:

[ConnieMaldonado]

When I change the date in the slicer, the include indicator doesn't change and all records are "1".  Any idea why that is?  I'll take a look and see if I can figure it out.