- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Calculating hours worked

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Calculating hours worked

01-05-2023
11:54 PM

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.

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-06-2023
02:13 AM

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

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

**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

))

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

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-08-2023
05:41 AM

Was this solved? Anyone has thoughts on this ?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-06-2023
02:13 AM

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

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

**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

))

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-06-2023
06:51 AM

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.

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 :).

Announcements

Check out the October 2024 Power BI update to learn about new features.

Learn from experts, get hands-on experience, and win awesome prizes.

Featured Topics

Top Solution Authors

User | Count |
---|---|

110 | |

96 | |

90 | |

81 | |

69 |

Top Kudoed Authors

User | Count |
---|---|

157 | |

125 | |

116 | |

111 | |

95 |