topic Sum if with two different columns in Developer

Sum if with two different columns

TiresAndMore — Thu, 19 Oct 2023 16:56:40 GMT

I know how I would do this in excel with just a couple of sumif statements but I can't get that same result in BI with filters. I have two columns representing Mechanic 2 and Mechanic 3. A mechanic can be listed as either M2 or M3 on any line item so I'm trying to total the labor column for a specific mechanic based off both columns. I don't want to use a measure because it needs to be dynamic with the date changes and the selected mechanic.

this is the structure of the table. So if trans.mech2 = 41 or trans.mech3 = 41 sum tech labor $ is what i'm needing.

Re: Sum if with two different columns

lbendlin — Sat, 21 Oct 2023 23:34:54 GMT

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Sum if with two different columns

Anonymous — Tue, 24 Oct 2023 08:08:47 GMT

Hi TiresAndMore ,

I suggest you to create a measure as below.

Sum if = CALCULATE ( SUM ( 'TableName'[Tech Labor $] ), FILTER ( 'TableName', OR ( 'TableName'[TRANS.MECH2] = 41, 'TableName'[TRANS.MECH3] = 41 ) ) )

My Sample:

Result is as below.

Best Regards,
Rico Zhou

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