cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EpicTriffid
Level: Powered On

Taking First value of individuals, then adding those sums together

Hi there,

 

I would like to be able to SUM the First record of Assessment Hours for each individual, then add those sums together. This is so I don't duplicate figures in my totals. 

 

 

Capture

So, Gayle would have 40, Christine would have 90, and in the Teaching Assessment Box above it would show 130. I can't seem to get First and Sum to cooperate in my formulas. I've tried:

 

Sum(ueUnits,Sum(LookUp(ueUnits,MMU_ID = 'UE Gal Staff ID Value'.Text)First('Assessment Hours'))

But I am very aware that is all kinds of wrong! Is what I want even possible?

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: Taking First value of individuals, then adding those sums together

Hey @EpicTriffid 

 

Can you try to update your expression as:
Sum(AddColumns(GroupBy('Unit Gal'.AllItems,"Name","Grouped"),"Score",First(Sort(Grouped,'Assessment Hours',Descending)).'Assessment Hours'),Score)
 
Here, Name needs to be replaced with the column name that holds Staff Name.
 
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

7 REPLIES 7
PavanKumar
Level 8

Re: Taking First value of individuals, then adding those sums together

Hi, Somehow am bit confused with the question. Is it possible for you to elaborate? Regards, Pavan Kumar Garlapati
PavanKumar
Level 8

Re: Taking First value of individuals, then adding those sums together

Hi, Somehow am bit confused with the question. Is it possible for you to elaborate? Regards, Pavan Kumar Garlapati

EpicTriffid
Level: Powered On

Re: Taking First value of individuals, then adding those sums together

Hi @PavanKumar 

 

So, I can have multiple records relating to different things, but because of the constraints I'm working under with the database, many of them will have the numbers repeated. For example, in my gallery, I might have the below:

 

Matt           50

Connor      90

Connor      90

Matt          50

Connor      90

 

Now, I need to know the value for each person, but not the sum, as I would be multiplying and that would give me a wrong number. So, essentially I need to say, for each name, give me the first number, which in this case would be:

 

Matt          50

Connor      90

 

Then add those together:

 

Total: 140

 

I hope that makes a little more sense. I think I need to use some combination of filter, first and sum, but I don't know in what order!

Highlighted
PavanKumar
Level 8

Re: Taking First value of individuals, then adding those sums together

HI @EpicTriffid ,

 

Think you can get the distinct records and sum it for example

 

 Distinct( Units, Countcolumn )

 

Then sum it up

 

-------------------------------------------------------------------------------------------

if this answers your question mark it as verified and give it a thumbsup

Regards,

Pavan Kumar Garlapati

EpicTriffid
Level: Powered On

Re: Taking First value of individuals, then adding those sums together

Hmm, I can't seem to get it to work

 

Sum('Unit Gal'.AllItems,Distinct('Unit Gal'.AllItems,'Hours'))
Dual Super User
Dual Super User

Re: Taking First value of individuals, then adding those sums together

Hey @EpicTriffid 

 

Can you try to update your expression as:
Sum(AddColumns(GroupBy('Unit Gal'.AllItems,"Name","Grouped"),"Score",First(Sort(Grouped,'Assessment Hours',Descending)).'Assessment Hours'),Score)
 
Here, Name needs to be replaced with the column name that holds Staff Name.
 
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

EpicTriffid
Level: Powered On

Re: Taking First value of individuals, then adding those sums together

Yup. Could totally kiss you right now!

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,495)