Flexible Scoring

[krixtsup3r]

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

[amitchandak]

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 Linkedin

[krixtsup3r]

@amitchandak 

Thank 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=gxtgucrt0qrtt3s3s54nkv... 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.

[amitchandak]

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.

[amitchandak · Accepted solution]

I did some data modeling. Please download the file from same location and try.

[krixtsup3r]

Thank you!