Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
You may add the following measure.
Measure = DIVIDE ( SUMX ( VALUES ( Table1[Full Name] ), CALCULATE ( MAX ( Table1[Total Time] ) ) ), DISTINCTCOUNT ( Table1[Full Name] ) * 40 )
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |