Forum Discussion
Flexible Scoring
Hello,
Can someone help me creating a flexible scoring measure?
- The data will be summarized by Supervisor.
- There's a date filter to show different coverages
- Handle time will be computed as (Worked Hours*3600)/Units
- Counts how many employee passed the target for Units & Worked Hours
- The target for Units & Worked Hours is the average of the metric
Below is the sample data. Thank you so much for your help!
| Date | ID | Employee Name | Supervisor | Units | Worked Hours |
| 1/1/2020 | 4622 | Chelsey Sweeney | Solomon Hoffman | 7258 | 119.5039 |
| 1/1/2020 | 3965 | Chanel Pate | Dorian Rivas | 7149 | 100.7825 |
| 1/1/2020 | 5313 | Matylda Muir | Samiya Leonard | 7042 | 113.0294 |
| 1/2/2020 | 6599 | Neha Weeks | Konrad Vasquez | 7031 | 155.2603 |
| 1/1/2020 | 5664 | Reeva Humphreys | Aria Campos | 7030 | 96.31583 |
| 1/2/2020 | 1805 | Jordi Roth | Keanu Ingram | 7026 | 87.44667 |
| 1/3/2020 | 2615 | Jace Hewitt | Sammy-Jo Newton | 6885 | 175.0389 |
| 1/4/2020 | 3359 | Nur Rhodes | Hanifa Mendez | 6744 | 117.8289 |
| 1/3/2020 | 1361 | Antoinette Franco | Solomon Hoffman | 6542 | 166.8425 |
| 1/3/2020 | 2327 | Emmy Wilder | Dorian Rivas | 6510 | 149.8561 |
| 1/4/2020 | 4032 | Brogan Floyd | Samiya Leonard | 6331 | 72.52278 |
| 1/5/2020 | 1172 | Niko Knott | Konrad Vasquez | 6290 | 80.66 |
| 1/5/2020 | 5375 | Lori Garcia | Aria Campos | 6070 | 63.77194 |
| 1/5/2020 | 5811 | Courteney Mcnamara | Keanu Ingram | 5871 | 86.25472 |
| 1/6/2020 | 1925 | Boyd Mueller | Sammy-Jo Newton | 5867 | 118.6203 |
| 1/6/2020 | 6602 | Sophia-Rose Gilmore | Hanifa Mendez | 5761 | 70.82778 |
| 1/7/2020 | 6784 | Roger Whyte | Everly Calderon | 5738 | 87.44472 |
| 1/8/2020 | 4693 | Cherry Hanson | Akaash Lake | 5722 | 156.1253 |
| 1/8/2020 | 2012 | Lucille Phelps | Samiya Leonard | 5648 | 118.0678 |
Sample Expected Output:
**All of the data below will change based on the selected date range
| Average Unit | 6448.16 |
| Average Handle Time | 112.43 |
| Row Labels | Pass Unit? | Pass Handle Time? | Total Employee |
| Akaash Lake | 0 | 1 | 1 |
| Aria Campos | 1 | 0 | 2 |
| Dorian Rivas | 2 | 1 | 2 |
| Everly Calderon | 0 | 0 | 1 |
| Hanifa Mendez | 1 | 1 | 2 |
| Keanu Ingram | 1 | 0 | 2 |
| Konrad Vasquez | 1 | 1 | 2 |
| Samiya Leonard | 1 | 2 | 3 |
| Sammy-Jo Newton | 1 | 2 | 2 |
| Solomon Hoffman | 2 | 2 | 2 |
| Total | 10 | 10 | 19 |
I did some data modeling. Please download the file from same location and try.
5 Replies
Please find the solution at : https://www.dropbox.com/s/2jb62pmfcy4ea5k/Flexible-Scoring.pbix?dl=0
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on LinkedinThank you so much!
I have tried the solution you have given and it's was fine, though when I applied it to my actual data it doesn't get what I wanted. I think my explanation was bad, my apologies about it.
In lieu with that please see this link https://www.dropbox.com/scl/fi/vcj58m3lcxw7qyyytsgw7/data_new.xlsx?dl=0&rlkey=gxtgucrt0qrtt3s3s54nkv0if for the excel file to what I really want to happen (column G-H) and how I want it to work.
Again my apologies and thank you for your support.
I think if the issue with the filter applied. This what you need to do. As we are using a simple table to get the overall average, we are forced take all of that table means the filter will not apply. Now create a date table and join with your date. Also, create an employee and Supervisor, using summarize of distinct and try. That should help.