Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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 NameTeamWeekTimeTotal Time
MikeFulham12040
MikeFulham11040
MikeFulham11040
DanFulham11540
DanFulham12040
DanFulham1540
DanFulham1640
DanFulham1740
CianMan Utd150
CianMan Utd160
CianMan Utd170
CianMan Utd120
ConorMan Utd12040
ConorMan Utd12040
1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@Anonymous 

 

You may add the following measure.

Measure =
DIVIDE (
    SUMX ( VALUES ( Table1[Full Name] ), CALCULATE ( MAX ( Table1[Total Time] ) ) ),
    DISTINCTCOUNT ( Table1[Full Name] ) * 40
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@Anonymous 

 

You may add the following measure.

Measure =
DIVIDE (
    SUMX ( VALUES ( Table1[Full Name] ), CALCULATE ( MAX ( Table1[Total Time] ) ) ),
    DISTINCTCOUNT ( Table1[Full Name] ) * 40
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.