Formula Help to overcome Duplicates?!

[Anonymous]

Hello everyone!

I would appreciate your assistance greatly.

Below is an example of my table in BI.

Each team member logs a Time record. If the Total Time for the person adds to >40 for the week, I have a formula to cap it at 40. If the team member logs under a sum of 40 for the week, then the Total Time is 0.

For example Dan, from the team Fulham, has logged a total of 53 hours for Week 1 but it's capped at 40. In contrast, Cian from Man Utd has logged only 20 hours so the Time Total goes to 0.

What I need to do is come up with a formula without duplicates for the week:

Sum of Total Time per team/(count of full names*40)

So for Fulham for week 1:

40+40 (Dan and Mike)/(2*40)  = 1

Thanks

Tom

Full Name	Team	Week	Time	Total Time
Mike	Fulham	1	20	40
Mike	Fulham	1	10	40
Mike	Fulham	1	10	40
Dan	Fulham	1	15	40
Dan	Fulham	1	20	40
Dan	Fulham	1	5	40
Dan	Fulham	1	6	40
Dan	Fulham	1	7	40
Cian	Man Utd	1	5	0
Cian	Man Utd	1	6	0
Cian	Man Utd	1	7	0
Cian	Man Utd	1	2	0
Conor	Man Utd	1	20	40
Conor	Man Utd	1	20	40

[v-chuncz-msft · Accepted solution]

@Anonymous 

You may add the following measure.

Measure =
DIVIDE (
    SUMX ( VALUES ( Table1[Full Name] ), CALCULATE ( MAX ( Table1[Total Time] ) ) ),
    DISTINCTCOUNT ( Table1[Full Name] ) * 40
)

[Anonymous]

Oh, you are a star!

Thank you so much.

May I ask for a quick explanation of the formula? What is the 'values' portion of the formula doing?

Thank you again