cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBi_Xandar
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 Comp1QtyComp1 LengthComp1 - Material Comp2QtyComp2 LengthComp2 - Material Comp3QtyComp3 LengthComp3 - Material Total Length 
aea110wood213wood110wood33
bad212paper111paper212paper35
cca115wood110wood115glass25
ded120flute113flute020flute33
efa225wood115glass215glass25
fab110paper211paper110paper31
1 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)))
Dinesh_Suranga_0-1664488024441.png

If this formula works, Please accept this as solution.

Thank you.

 

View solution in original post

4 REPLIES 4
Dinesh_Suranga
Responsive Resident
Responsive Resident

@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.

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

@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)))
Dinesh_Suranga_0-1664488024441.png

If this formula works, Please accept this as solution.

Thank you.

 

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.