cancel
Showing results for
Did you mean:
Frequent Visitor

## Sum values of multiple columns per row with condition

Hi ,
I have a dataset , which is shown below,
In this we need to create calculate column in power bi for total length of products based on few conditions,

**
if Comp1Qty > 0, Comp2Qty > 0
and Comp1Material = Comp2Material
then Comp1Lenght +Comp2Length
or
Comp1Qty > 0, Comp2Qty > 0, Comp3Qty > 0
and Comp1Material = Comp2Material = Comp3Material
then Comp1Lenght +Comp2Length+ Comp3Length
else if
Comp1Qty > 0 but materials are not equal then Comp1Lenght

 Product Type Comp1Qty Comp1 Length Comp1 - Material Comp2Qty Comp2 Length Comp2 - Material Comp3Qty Comp3 Length Comp3 - Material Total Length a ea 1 10 wood 2 13 wood 1 10 wood 33 b ad 2 12 paper 1 11 paper 2 12 paper 35 c ca 1 15 wood 1 10 wood 1 15 glass 25 d ed 1 20 flute 1 13 flute 0 20 flute 33 e fa 2 25 wood 1 15 glass 2 15 glass 25 f ab 1 10 paper 2 11 paper 1 10 paper 31
1 ACCEPTED SOLUTION
Responsive Resident

Hi,

Total Length = IF(SUM(Table1[Comp1Qty]) > 0 && SUM(Table1[Comp2Qty]) > 0 && SELECTEDVALUE(Table1[Comp1 - Material ]) = SELECTEDVALUE(Table1[Comp2 - Material ]),
Sum(Table1[Comp1 Length]) + Sum(Table1[Comp2 Length]) ,
IF(SUM(Table1[Comp1Qty]) > 0 && SUM(Table1[Comp2Qty]) > 0 && SUM(Table1[Comp3Qty]) > 0 &&  SELECTEDVALUE(Table1[Comp1 - Material ]) = SELECTEDVALUE(Table1[Comp2 - Material ]) && SELECTEDVALUE(Table1[Comp1 - Material ]) = SELECTEDVALUE(Table1[Comp3 - Material ])  ,
Sum(Table1[Comp1 Length]) + Sum(Table1[Comp2 Length]) + Sum(Table1[Comp3 Length]),
IF(SUM(Table1[Comp1Qty]) > 0 , Sum(Table1[Comp1 Length]),0)))

If this formula works, Please accept this as solution.

Thank you.

4 REPLIES 4
Responsive Resident

Hi

In your formula you have mentioned "if CompQty > 0".

There is no column called CompQty in your data set. How did you get that?

Thank you.

Frequent Visitor

It is  Comp1Qty >0 , Comp2Qty>0 , Comp3Qty >0  then proceed to second condition Comp1Material = Comp2Material = Comp3Material , to get the total.
For eg
If (
Comp1Qty >0 , Comp2Qty >0  &&
Comp1Material = Comp2Material)
then Comp1Lenght +Comp2Length

Responsive Resident

Hi,

Total Length = IF(SUM(Table1[Comp1Qty]) > 0 && SUM(Table1[Comp2Qty]) > 0 && SELECTEDVALUE(Table1[Comp1 - Material ]) = SELECTEDVALUE(Table1[Comp2 - Material ]),
Sum(Table1[Comp1 Length]) + Sum(Table1[Comp2 Length]) ,
IF(SUM(Table1[Comp1Qty]) > 0 && SUM(Table1[Comp2Qty]) > 0 && SUM(Table1[Comp3Qty]) > 0 &&  SELECTEDVALUE(Table1[Comp1 - Material ]) = SELECTEDVALUE(Table1[Comp2 - Material ]) && SELECTEDVALUE(Table1[Comp1 - Material ]) = SELECTEDVALUE(Table1[Comp3 - Material ])  ,
Sum(Table1[Comp1 Length]) + Sum(Table1[Comp2 Length]) + Sum(Table1[Comp3 Length]),
IF(SUM(Table1[Comp1Qty]) > 0 , Sum(Table1[Comp1 Length]),0)))

If this formula works, Please accept this as solution.

Thank you.

Frequent Visitor

Thank you 🙂 for the solution. 😀

Also we can create a calculated column
Total Length =
If (
Table1[Comp1Qty] >0 &&
Table1[Comp2Qty] >0 &&
Table1[Comp3Qty] >0 &&
Table1[Comp1 - Material] = Table1[Comp2 - Material ] &&
Table1[Comp1 - Material] = Table1[Comp3 - Material ],
Table1[Comp1 Length] + Table1[Comp2 Length] + Table1[Comp3 Length],
If (
Table1[Comp1Qty] >0 &&
Table1[Comp2Qty] >0 &&
Table1[Comp1 - Material] = Table1[Comp2 - Material ],
Table1[Comp1 Length] + Table1[Comp2 Length],
If (
Table1[Comp1Qty] >0,
Table1[Comp1 Length]
)))