Sum values of multiple columns per row with condition

[PowerBi_Xandar]

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

[Dinesh_Suranga]

@PowerBi_Xandar 

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.

[PowerBi_Xandar]

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

[Dinesh_Suranga · Accepted solution]

@PowerBi_Xandar 

Hi,

Please use following DAX formula.

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.

[PowerBi_Xandar]

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]
)))