If Help

[Anonymous]

Hello,

I am having isues with creating a new column using mutiple columns with an IF statement. I am looking to do something like this

If timeissue = good and week = 1 then sum actual hours, if timeissue = missing timesheet then missing time sheet.

I want this calculated in a new column. Any suggestions? Thanks

[Anonymous]

Hi, @Anonymous 

Hope this is what you are looking for:

Result =
SWITCH (
    [Time Issue],
    "Good", SWITCH ( Sheet1[Week], 1, FORMAT ( SUM ( Sheet1[Actual Hours] ), BLANK () ) ),
    "Missing Timesheet", "Missing Timesheet"
)

The result should be similar to my sample table:

Best,

Paul

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[venal]

@Anonymous 

I have tried to re-pro the issue with the sample data.

Can you please use the below one for your requirment.

HoursCalc = IF(Data[TimeIssue]="Good" && Data[Week]=1,Data[ActualHours],IF(Data[TimeIssue]="Missing Timesheet",Data[ActualHours],0))

Note:- If timeissue = good and week = 1 then sum actual hours, if timeissue = missing timesheet then missing time sheet. (I am not clear on the high lighted one, based on business needs, edit the above condition)

If you have any queries, please let us know.

If this post helps, then please consider Accept it as the solution to help the other members find it more
If this post was helpful may I ask you to mark it as solution and click on thumb symbol?

[Anonymous]

Thank you for the reply. So I would want the new column to either show the sum of hours or show missing timesheet, pending approval,or recently approved which would be under time issue. How would I go about that?

[Anonymous]

@Anonymous 

Just try use the provideded to create a calculate column.

Best, 
Paul

[Anonymous]

Hello,

The formual is working but it is summing all of the hours for week 1 in all of the columns that correlate to good. I need this to sum individually for each person in week 1. Not overall. Essentially I am calculatiing week 1 based on the week of the month. So fi we using this current month of November for example today 11/25/19 would be week4.  Maybe I need to do this as a Measure so it will calculate based on the week within the month? Any suggestions?

[Anonymous · Accepted solution]

@Anonymous 

Result =
SWITCH (
    [Time Issue],
    "Good", SWITCH (
        Sheet1[Week],
        1, FORMAT (
            CALCULATE ( SUM ( Sheet1[Acutaul Hour] ), FILTER ( Sheet1, Sheet1[Week] = 1 ) ),
            BLANK ()
        )
    ),
    "Missing Timesheet", "Missing Timesheet"
)

Paul

[Anonymous]

Go to  home-> edit queries -> Add column -> Conditional Column

Give a try!

Eric Ji | Senior Business Intelligence Consultant
www.designmind.com