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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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