Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
89 | |
86 | |
75 | |
69 | |
68 |
User | Count |
---|---|
220 | |
128 | |
117 | |
82 | |
77 |