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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Coryanthony
Helper III
Helper III

Calculating hours worked

Hi,

I am attemping to calculate the sum of allowed $ based on hours worked on a given day. I currently enter time on several rows since different chargecodes are involved.

7/1/2022 | 5 hours

7/1/2022 | 3 hours

7/1/2022 | 2 hours

Then i am calculating the sum based on day, this determines the allowed $. Since I work over 10hrs on 7/1/22, I should be allowed 32, instead its multiplying by 3 (# of rows). Displaying my allowed as 96. 

 

Additionally, for the days where no time(hours) was entered, i am getting a blank. I would prefer to be a 0 rather than empty/blank.

 

Coryanthony_0-1672991544562.png

 

Coryanthony_1-1672991558217.png

 

 

My Current Allowed calculated column is: 

 

$Allowed =
var _sum = CALCULATE(SUM('Merge1'[Hours]),FILTER(ALLEXCEPT('Merge1','Merge1'[employee id],'Merge1'[date]),'Merge1'[Hours]))
return
 IF(_sum>=4 && _sum<8 && Merge1[Holiday] = "Yes",12, IF(_sum>=8 && Merge1[Holiday] = "Yes",32, IF(_sum>=10 &&'Merge1'[Day of Week]>=0 && 'Merge1'[Day of Week]<=4,20, IF(_sum>=4 && _sum<8 && 'Merge1'[Day of Week]>=5,12, IF(_sum>=8 && 'Merge1'[Day of Week]>=5,32,0)))))
 
 
 

Thank you for the assitance and time.

 

 

 

1 ACCEPTED SOLUTION
pi_eye
Resolver IV
Resolver IV

Hi @Coryanthony  

 

What's happening here is that the calculated column makes a value for $allowed on every single row of data. As there are 3 rows for 7/1/22, naturally there are 3 repeated values of $allowed.

When $Allowed is dropped into a table, it is a number and will be automatically aggregated which is causing those values to Sum. 

There are a few things you can do to change this:

 

1) Change the model so that there is a separate table dimension for employee & date

This will then return just the one value for every combination of the above
 
2) Create a measure that aggregates your existing $allowed column as appropriate
In the UI, create a measure. Here I've just taken a flat average
Allowed_Aggr = AVERAGE(Merge1[$Allowed])
You will need to tweak this to sum properly depending on the context that you are going to use. The average above will work for combinations of Employee & Date, but will need changing depending on how you handle totals
pi_eye_1-1672999032726.png

 

3) Change $Allocation to a measure rather than a calculated column

This could have performance implications, but the equivalent of your calculation above, as a measure would be:

Allowed_As_Measure = 
SumX(SUMMARIZE(
        Merge1,
        Merge1[Employee ID],Merge1[Date],Merge1[Holiday],Merge1[Day of Week],
        "DailyHrs",sum(Merge1[Hours])),
    SWITCH(TRUE,
        [DailyHrs]>=4 && [DailyHrs]<8 && Merge1[Holiday] = "Yes" ,12,
        [DailyHrs]>=8 && Merge1[Holiday] = "Yes" ,32,
        [DailyHrs]>=10 &&'Merge1'[Day of Week]<=4,20,
        [DailyHrs]>=4 && [DailyHrs]<8 && 'Merge1'[Day of Week]>=5,12,
        [DailyHrs]>=8 && 'Merge1'[Day of Week]>=5,32,
        0
    ))
pi_eye_2-1672999938929.png

 

In this, I am assuming that the totals need to be summed. I have also replaced the if statements with a switch() - this is a much cleaner way to introduce much logic.
 
 
In addition to the above, notice how changing to a measure introduces "0" instead of blank.
HTH
 
Pi

 

 
 
 
 

 

View solution in original post

4 REPLIES 4
thx
Frequent Visitor

@Coryanthony 

please let me know if you rectify. Thx 

thx
Frequent Visitor

Was this solved? Anyone has thoughts on this ? 

pi_eye
Resolver IV
Resolver IV

Hi @Coryanthony  

 

What's happening here is that the calculated column makes a value for $allowed on every single row of data. As there are 3 rows for 7/1/22, naturally there are 3 repeated values of $allowed.

When $Allowed is dropped into a table, it is a number and will be automatically aggregated which is causing those values to Sum. 

There are a few things you can do to change this:

 

1) Change the model so that there is a separate table dimension for employee & date

This will then return just the one value for every combination of the above
 
2) Create a measure that aggregates your existing $allowed column as appropriate
In the UI, create a measure. Here I've just taken a flat average
Allowed_Aggr = AVERAGE(Merge1[$Allowed])
You will need to tweak this to sum properly depending on the context that you are going to use. The average above will work for combinations of Employee & Date, but will need changing depending on how you handle totals
pi_eye_1-1672999032726.png

 

3) Change $Allocation to a measure rather than a calculated column

This could have performance implications, but the equivalent of your calculation above, as a measure would be:

Allowed_As_Measure = 
SumX(SUMMARIZE(
        Merge1,
        Merge1[Employee ID],Merge1[Date],Merge1[Holiday],Merge1[Day of Week],
        "DailyHrs",sum(Merge1[Hours])),
    SWITCH(TRUE,
        [DailyHrs]>=4 && [DailyHrs]<8 && Merge1[Holiday] = "Yes" ,12,
        [DailyHrs]>=8 && Merge1[Holiday] = "Yes" ,32,
        [DailyHrs]>=10 &&'Merge1'[Day of Week]<=4,20,
        [DailyHrs]>=4 && [DailyHrs]<8 && 'Merge1'[Day of Week]>=5,12,
        [DailyHrs]>=8 && 'Merge1'[Day of Week]>=5,32,
        0
    ))
pi_eye_2-1672999938929.png

 

In this, I am assuming that the totals need to be summed. I have also replaced the if statements with a switch() - this is a much cleaner way to introduce much logic.
 
 
In addition to the above, notice how changing to a measure introduces "0" instead of blank.
HTH
 
Pi

 

 
 
 
 

 

Hi @pi_eye 

I love the measure! It is doing exactly what i've spent half of day attemping to figure out. 

Follow up question if I may. On days an employee submits >1 expense, on a different report number, it is doubling the allowed amount. Please see snippet. would you happen to have any solution for this? I would like it to only display the allowed amount for that day (not based on amount of reports submitted. 

 

Coryanthony_0-1673016453301.png

On 11/22/2022, 2 reports were submitted; therefore, it is calculating $20 for both reports, rathan than only $20 for that day.

 

Additionally, it appears i am getting a blank rather than 0 on days time was not supposed.

 

Thank you again for your time, much appreciated :).

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.