Forum Discussion

Coryanthony's avatar
Coryanthony
Icon for Helper III rankHelper III
3 years ago
Solved

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.

 

$ Allow = CALCULATE(SUM(Timesheet[Number (unit)]), IF(Timesheet[Day of Week] >=0 && Timesheet[Day of Week] <=4 && Timesheet[Number (unit)] >10, 20,0))
 
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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. 

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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. 

    • Coryanthony's avatar
      Coryanthony
      Icon for Helper III rankHelper III
      3 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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. 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Show the expected result very clearly.  Share the download link of the PBI file.

    • Coryanthony's avatar
      Coryanthony
      Icon for Helper III rankHelper III
      3 years ago

      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