Forum Discussion
Formula Help to overcome Duplicates?!
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 |
Anonymous
You may add the following measure.
Measure = DIVIDE ( SUMX ( VALUES ( Table1[Full Name] ), CALCULATE ( MAX ( Table1[Total Time] ) ) ), DISTINCTCOUNT ( Table1[Full Name] ) * 40 )
2 Replies
Anonymous
You may add the following measure.
Measure = DIVIDE ( SUMX ( VALUES ( Table1[Full Name] ), CALCULATE ( MAX ( Table1[Total Time] ) ) ), DISTINCTCOUNT ( Table1[Full Name] ) * 40 )