Forum Discussion
Helper IINew Column with IF STATEMENT
Hi Folks,
I have a table with some columns as below. The print screen below is a table from Power BI. This is perfect if I just want to see each employees' hours on a daily basis. However, I would like to calculate their over time hours. I want to add a column name OT and the formula if it's on Excel, it would be like this:
OT = If("Sum of Hours">8, "Sum of Hours" - 8, "No OT")
Other words, the 1 to 3 rows with 8 hours, the "OT" column will show "No OT" for the 9 hours will show 1.
How do I do this on Power BI? Do I need to write DAX? or I can do the quick measure? Please help. Thank you.
This is the data source (Table)
is this what you want?
Column 2 = if('Table'[Column]="OT", CALCULATE(sum('Table'[Hours]),ALLEXCEPT('Table','Table'[Date],'Table'[Employee]))-8,0)pls provide the sample data
6 Replies
ryan_mayuSuper User
you can try this to create a column
Column = if(CALCULATE(sum('Table'[Hours]),ALLEXCEPT('Table','Table'[Date],'Table'[Employee]))>8,"OT","NOT OT")
PowerBIFreakThank you very much, it works perfect. Now, I want to calculate how many OT hours for each employee. I've tried something like this to create a new column but of course it didn't work.
Drivers' OT = IF('Table'[column]=OT, (SUM('Table'[Hours],ALLEXCEPT('Table','Table'[Date],'Table'[Employee]))-8, 0)For example, Paul has 1 hour OT. How I interpret this is if the Column said OT, then I will add up his total hours for him than minus 8 because anything that is exceed 8 hours is OT; otherwise, 0. So, on my new column that named "Drivers' OT" I will have 1 for Paul. Would you mind to help me with the Dax I had?I appreciate your time.- ryan_mayu
is this what you want?
Column 2 = if('Table'[Column]="OT", CALCULATE(sum('Table'[Hours]),ALLEXCEPT('Table','Table'[Date],'Table'[Employee]))-8,0)