- Power BI forums
- Updates
- News & Announcements
- 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
- Custom Visuals 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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- 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 September 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 |
---|---|

104 | |

100 | |

99 | |

38 | |

37 |

Top Kudoed Authors

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

158 | |

124 | |

75 | |

74 | |

63 |