Forum Discussion
Sum if with two different columns
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.
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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/1447523Hi 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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
