cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NaderSaeed
Helper I
Helper I

return sum if value into the related row

Hello all,

 

Sheet 1

LineTotal DurationPlannedUnplanned
14.4  

2

3.7  
31.5  
42.5  
55.1  

Sheet 2

LineDurationPlanned/Unplanned
10.2Planned
40.22Planned
40.4Unplanned
20.75PlannedPlanned
50.45Unplanned
10.3Unplanned
30.2Planned
20.33Unplanned

 

I want to return the sum of "Duration" for all "Planned" from Sheet 2 for each Line into the "Planned" coulmn of Sheet 1, and the same for the sum of "Duration" for all "Unplanned" from Sheet 2 for each Line into the "Unplanned" coulmn of Sheet 1.

 

How could it be done using Summarize or any other function?

 

Thanks.

1 ACCEPTED SOLUTION

My mistake && in place of,

Planned in Sheet 1 = sumxx(filter(sheet2, sheet1[line]=sheet2[line] && sheet2[planned/uplanned]= "Planned"), sheet2[duration]))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
amitchandak
Super User IV
Super User IV

Try

Planned in Sheet 1 = sumxx(filter(sheet2, sheet1[line]=sheet2[line], sheet2[planned/uplanned]= "Planned"), sheet2[duration]))

UnPlanned in Sheet 1 = sumxx(filter(sheet2, sheet1[line]=sheet2[line], sheet2[planned/uplanned]= "Unplanned"), sheet2[duration]))

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks for replying.

 

Planned in Sheet 1 = sumxx(filter(sheet2, sheet1[line]=sheet2[line], sheet2[planned/uplanned]= "Planned"), sheet2[duration]))

 

The red part doesn't get referenced.

 

by sumxx you mean sumx? or am i missing something?

My mistake && in place of,

Planned in Sheet 1 = sumxx(filter(sheet2, sheet1[line]=sheet2[line] && sheet2[planned/uplanned]= "Planned"), sheet2[duration]))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Absolute Legend, Thanks 🙂

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

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

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.