cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
nuhasan
Helper IV
Helper IV

Conditional Column with sum function

Hi all,

 

Want to ask how can I do a formulation for STOP TIME column as per below excel formula. Already done successfully in DAX but I want to do it in the power query (M language) since I want to group it later.

 

Logic formulation as below (understand power query cannot do or function more than 2)

 

1. if value key "ZN04" or "ZN05" then sum(activity 1,activity 2,activity 3,activity 4,activity 5,activity 6) else 0

2. if value key "ZN19" or "ZN20" then sum(activity 1,activity 2,activity 3,activity 4,activity 5,activity 6) else 0

 

 

Stop TimeValue KeyActivity 1Activity 1 - UnitActivity 2Activity 2 - UnitActivity 3Activity 3 - UnitActivity 4Activity 4- UnitActivity 5Activity 5 - UnitActivity 6Activity 6 - Unit
 ZN0114.271H0.000 0.000 0.000 0.000 0.000 
 ZN0285.627H14.271H14.271H14.271H14.271H0.000 
0ZN040MIN0MIN0MIN0MIN0MIN0MIN
607ZN05431MIN0MIN0MIN176MIN0.000 0.000 
 ZN060.000 264.000M102.000M0.000 0.000 0.000 
 ZN070.000 0.000 0.000 0.000 0.000 0.000 
 ZN080.000 0.000 0.000 0.000 0.000 0.000 
 ZN090.000 0.000 0.000 0.000 0.000 0.000 
 ZN100.000 0.000 0.000 0.000 0.000 0.000 
 ZN110.000 0.000 0.000 0.000 0.000 0.000 
 ZN120.000 0.000 0.000 0.000 0.000 0.000 
 ZN130.000 0.000 463.000M0.000 0.000 0.000 
 ZN140.000 0.000 0.000 0.000 0.000 0.000 
 ZN150.000 0.000 0.000 0.000 0.000 0.000 
 ZN160.000 0.000 0.000 0.000 0.000 0.000 
 ZN170.000 0.000 0.000 0.000 0.000 0.000 
 ZN180.000 0.000 329.000M20.000M0.000 0.000 
1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

Use below formula in a custom column

= if List.Contains({"ZN04","ZN05","ZN19","ZN20"},[Value Key]) then List.Sum({[Activity 1],[Activity 2],[Activity 3],[Activity 4],[Activity 5],[Activity 6]}) else 0

View solution in original post

2 REPLIES 2
Vijay_A_Verma
Super User
Super User

Use below formula in a custom column

= if List.Contains({"ZN04","ZN05","ZN19","ZN20"},[Value Key]) then List.Sum({[Activity 1],[Activity 2],[Activity 3],[Activity 4],[Activity 5],[Activity 6]}) else 0

hI @Vijay_A_Verma ,

 

Thank you so much for your support. Case solved!

 

Regards,

Nuha

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors