- 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
Sum hours based on day, then create new column with IF function
Hello,
I want to sum up hours worked base on day, then create a column ($Allowed) base on the sum of hours.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Coryanthony ,
This is due to the nature of calculated columns, and you can create a table visual to show.
You can also create a measure.
measure_Allowed =
var _sum = CALCULATE(SUM('Table'[number (unit)]),FILTER(ALLEXCEPT('Table','Table'[employee id],'Table'[date]),'Table'[number (unit)]))
return
IF(_sum>10&&max('Table'[Day of Week])>=0&&MAX('Table'[Day of Week])<=4,20,0)
Please refer to my PBIX file.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Show the expected result very clearly. Share the download link of the PBI file.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thank you for your input Ashish. I am learning more about community.powerbi.
If you have any suggestion, please feel free to share. Thank you
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Coryanthony ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create a calculated column.
$Allowed =
var _sum = CALCULATE(SUM('Table'[number (unit)]),FILTER(ALLEXCEPT('Table','Table'[employee id],'Table'[date]),'Table'[number (unit)]))
return
IF(_sum>10&&'Table'[Day of Week]>=0&&'Table'[Day of Week]<=4,20,0)
(3) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Neeko,
Thank you so much for your time and well written explanation, it honestly means a lot.
Your solution worked! Only issue - its currently allowing $20 multiple times for same day. If you look at the snippet you provided for date "2022-12-13", it duplicated the $20. I assume the only work around is to delete the duplicates?
I am faily new to power BI, but doing my very best to learn. If there are any tools/courses/classes you can suggest, that would be extremely awesome. If you also know of any personal tutors, please please share.
Thank you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Coryanthony ,
This is due to the nature of calculated columns, and you can create a table visual to show.
You can also create a measure.
measure_Allowed =
var _sum = CALCULATE(SUM('Table'[number (unit)]),FILTER(ALLEXCEPT('Table','Table'[employee id],'Table'[date]),'Table'[number (unit)]))
return
IF(_sum>10&&max('Table'[Day of Week])>=0&&MAX('Table'[Day of Week])<=4,20,0)
Please refer to my PBIX file.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
one last thing Neeko, if i need to add more IF/logical statement to this such as, "
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please help if all possible. Thank you
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
06-06-2024 02:11 AM | |||
03-14-2024 10:56 PM | |||
06-11-2024 12:36 AM | |||
08-01-2023 08:17 AM | |||
03-26-2024 03:55 PM |
User | Count |
---|---|
113 | |
77 | |
55 | |
54 | |
43 |
User | Count |
---|---|
183 | |
120 | |
80 | |
67 | |
57 |