Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Continued Contributor
Continued Contributor

@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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.