Sum values from different columns based on another columns value

(0) Share

Report

Posted on by LennartWalldén

181

Hi all,

I have a Project Management list where every project has a StartYear and a StopYear. From that I have set columns with the actual year, and the actual budget. See figure.

Budget data.png

Now want to add the yearly project budget for a certain year to each other to see our total project volume year by year.

I would like to have a Collection with five columns which starts with current year and e.g four years ahead.

Does anyone have a smart solution for this?

Best,
Lennart

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

WarrenBelz 156,117 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @LennartWalldén ,
Something like this in a Gallery Items should work
AddColumns( GroupBy( YourListName, "StartYear", "YearBudget" ), "BudgetTotal", YearBudget.BudgetYear1 + YearBudget.BudgetYear2 + YearBudget.BudgetYear3 + YearBudget.BudgetYear4 + YearBudget.BudgetYear5 )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
LennartWalldén 181 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WarrenBelz,

Thanks for your reply.
I am sorry, but I don't think I made myself clear in the previous post. The result I want is the sum of the actual years yearly budget from each project.
So for example, if we look at 2020 and the figure, the result should be:
Project1.BudgetYear3+Project2.BudgetYear2+Project3.BudgetYear1+Project4.BudgetYear1+Project5.BudgetYear2.

Do you see what I mean? 🙂

Best,
Lennart

Was this reply helpful? Yes No
WarrenBelz 156,117 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

@LennartWalldén ,
A job for a spreadsheet I think - nothing I can think of sorry. Someone else may want to attempt it.

Was this reply helpful? Yes No
LennartWalldén 181 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WarrenBelz ,

Thanks for your reply. In some way it feels good that I'm not the only one who can't figure this out 🤔, and I assume that if you can't it's more or less impossible. I will try to have some conversation between a spreadsheet and my app, as you suggested.

Thanks anyhow for your attempt!😊

Was this reply helpful? Yes No
Verified answer

v-yutliu-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @LennartWalldén ,

Does your original table looks like this?

Do you want a table that looks like this?

Could you tell me the data type of the fields in original table?
I assume that except projectname field, others are all number type.

If so, try this formula to create this kind of collection:

ClearCollect(yearlybudget, {Year:Year(Today()), Totalproj:Sum(Filter('Project Management',Year1=Year(Today())),BudgetYear1)+ Sum(Filter('Project Management',Year2=Year(Today())),BudgetYear2)+ Sum(Filter('Project Management',Year3=Year(Today())),BudgetYear3)+ Sum(Filter('Project Management',Year4=Year(Today())),BudgetYear4)+ Sum(Filter('Project Management',Year5=Year(Today())),BudgetYear5)}, {Year:Year(Today())+1, Totalproj:Sum(Filter('Project Management',Year1=Year(Today())+1),BudgetYear1)+ Sum(Filter('Project Management',Year2=Year(Today())+1),BudgetYear2)+ Sum(Filter('Project Management',Year3=Year(Today())+1),BudgetYear3)+ Sum(Filter('Project Management',Year4=Year(Today())+1),BudgetYear4)+ Sum(Filter('Project Management',Year5=Year(Today())+1),BudgetYear5)}, {Year:Year(Today())+2, Totalproj:Sum(Filter('Project Management',Year1=Year(Today())+2),BudgetYear1)+ Sum(Filter('Project Management',Year2=Year(Today())+2),BudgetYear2)+ Sum(Filter('Project Management',Year3=Year(Today())+2),BudgetYear3)+ Sum(Filter('Project Management',Year4=Year(Today())+2),BudgetYear4)+ Sum(Filter('Project Management',Year5=Year(Today())+2),BudgetYear5)}, {Year:Year(Today())+3, Totalproj:Sum(Filter('Project Management',Year1=Year(Today())+3),BudgetYear1)+ Sum(Filter('Project Management',Year2=Year(Today())+3),BudgetYear2)+ Sum(Filter('Project Management',Year3=Year(Today())+3),BudgetYear3)+ Sum(Filter('Project Management',Year4=Year(Today())+3),BudgetYear4)+ Sum(Filter('Project Management',Year5=Year(Today())+3),BudgetYear5)}, {Year:Year(Today())+4, Totalproj:Sum(Filter('Project Management',Year1=Year(Today())+4),BudgetYear1)+ Sum(Filter('Project Management',Year2=Year(Today())+4),BudgetYear2)+ Sum(Filter('Project Management',Year3=Year(Today())+4),BudgetYear3)+ Sum(Filter('Project Management',Year4=Year(Today())+4),BudgetYear4)+ Sum(Filter('Project Management',Year5=Year(Today())+4),BudgetYear5)} )

Best regards,

Was this reply helpful? Yes No
LennartWalldén 181 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-yutliu-msft ,

Thanks a lot, it worked like a charm! 😄

Best,
Lennart

Was this reply helpful? Yes No