RANKX with filter

[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.

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

I'm not sure how to structure the RANKX function, but here's what I have so far:

# Techs by Location =
CALCULATE(
DISTINCTCOUNT(TechData[Employee_Number]),
ALLEXCEPT(TABLE,'DATE Dim'[DATE],TABLE[Location])
)

RANK No to Include = ROUND(# Techs by Location * .75, 0)

Any help would be appreciated!

[parry2k]

@ConnieMaldonado of course feel free to reach out if you have questions. If required, we can have a goto meeting and go over it. Cheers!! I love working on challenging stuff like this. 🙂

[parry2k · Accepted solution]

@ConnieMaldonado enjoyed doing this. check attached. I hope this is what you are looking for?

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[ConnieMaldonado]

Wow!  It works.  This is great.  I feel like I should send you a check.  🙂  You've been a big help, and you are AWESOME!  Thank you.  I'm still working through the logic, so I understand it.  It turned out to be quite a mind bender, but with a few steps, you made it work.  Thank you.  I'll let you know if I have any other questions, but it looks good.  

[parry2k]

@ConnieMaldonado ok good, I already have sorted out the 2nd sheet, so just wanted to do the final output and that's where I found this discrepancy. Do you want this to work based on the date filter? So for example if one location has 12 id's and filter on certain period, brings it down to 8 ids', do you want that 75% of this filtered count or based on the original count (without filter)

[ConnieMaldonado]

Based on new count - i.e., dynamic based on slicer dates

[parry2k]

@ConnieMaldonado sorry for the delay on this, I think I'm almost done but need to understand bit:

on the final output page, the first row, how come you have total of hours 91 for this loc1, id and date 

where if you look at raw data (first page in excel ) the total hours are 139.06 (Highlighted in yellow) for the same date:

Am I missing something or it is something else?

[ConnieMaldonado]

You are correct - I probably made a change and didn't update stuff when trying to "dummy" the data.  My apologies.  The desired output has records by ID, date - it represents the source data I have.  Since this tech is in the top 75% by tech count, all of his records will be included in the report.  I hope that makes sense. 🙂  Thank you so much!  I haven't been able to figure out the date thing - how to get the logic to work when you change the slicer dates.   It's a crazy way to weed out low-performers, but I have other reports I'll likely use the logic for.

[ConnieMaldonado]

Let me check it!  Hold on.

[ConnieMaldonado]

Here you go Parry2k:

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

I tried various combinations of using calculatetable, but couldn't get the result.  In the Google folder, I included a pbix file and a spreadsheet to make it clear.  I only want to include on my report techs who are in the top 75% of the count by location, based on hours during the timeframe entered in the slicer.  Whew!  It's alot, and possibly the most complicated thing I've done, so I really appreciate your help.  Let me know if you have an issue with the link.

[parry2k]

@ConnieMaldonado Hey, sorry. Missed that. Can you share a sample pbix file? You can share using One drive/google drive.

[ConnieMaldonado]

Here you go Parry2k:

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

I tried various combinations of using calculatetable, but couldn't get the result.  In the Google folder, I included a pbix file and a spreadsheet to make it clear.  I only want to include on my report techs who are in the top 75% of the count by location, based on hours during the timeframe entered in the slicer.  Whew!  It's alot, and possibly the most complicated thing I've done, so I really appreciate your help.  Let me know if you have an issue with the link.

[ConnieMaldonado]

Let me put one together in a few hours. It's late here now 🙂 I SO appreciate your help!

[parry2k]

@ConnieMaldonado just to make sure I understood it correctly. So you want to show 75% of technicians for each location, ranked based on their working hours between selected dates. Correct?

[ConnieMaldonado]

Hey Parry2k!!  I responded to your message below.  Any suggestions?  Thanks!!!

[ConnieMaldonado]

Yes!  You summarized it perfectly.  Thanks.