cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

If Help

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

 

Capture.PNG

 

1 ACCEPTED SOLUTION

@Rippey 

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

View solution in original post

7 REPLIES 7
Community Support
Community Support

Hi, @Rippey 

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:

CaSDFSDFSDF.JPG

Best,

Paul

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

Memorable Member
Memorable Member

@Rippey 

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?
Frequent Visitor

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?

@Rippey 


Just try use the provideded to create a calculate column.

Best, 
Paul

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?

@Rippey 

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

View solution in original post

Anonymous
Not applicable

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

 

Give a try!

 

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors