cancel
Showing results for
Did you mean:
Helper I

## return sum if value into the related row

Hello all,

Sheet 1

 Line Total Duration Planned Unplanned 1 4.4 2 3.7 3 1.5 4 2.5 5 5.1

Sheet 2

 Line Duration Planned/Unplanned 1 0.2 Planned 4 0.22 Planned 4 0.4 Unplanned 2 0.75 PlannedPlanned 5 0.45 Unplanned 1 0.3 Unplanned 3 0.2 Planned 2 0.33 Unplanned

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
Super User IV

My mistake && in place of,

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

Proud to be a Super User!

4 REPLIES 4
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

Proud to be a Super User!

Helper I

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?

Super User IV

My mistake && in place of,

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

Proud to be a Super User!

Helper I

Absolute Legend, Thanks 🙂

Announcements