cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate III
Advocate III

Calculate the average of a comma separated valuelist

I'm using this formula for a label

LookUp(prchk, Project_ID.Value = ThisItem.ID,'User ratings')

and it shows as a comma separated list in the label, e.g. 4,5,4,5,
using

LookUp(prchk, Project_ID.Value = ThisItem.ID, Left('User ratings', Len('User ratings')-1))

I can get rid of the trailing comma, so I assume 'User ratings' is a string indeed

However

Average(LookUp(prchk, Project_ID.Value = ThisItem.ID, Left('User ratings', Len('User ratings')-1)))

returns nothing, and

Split(LookUp(prchk, Project_ID.Value = ThisItem.ID, Left('User ratings', Len('User ratings')-1)),",")

results in an "Expected Text Value" error.


I understand this column type is not supported (for updating SP list from PowerApps), but if the returned value is a comma separated value string I can manipulate with Len(), shouldn't there be a way to calculate the average from that string, or splitting it into a table and then calculate the average on the value column?
I tried as well

Text(LookUp(prchk, Project_ID.Value = ThisItem.ID, Left('User ratings', Len('User ratings')-1)),Number)

Which works with both, textformat Text or Number, but still I can't neither average nor split it. Help would be appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver I
Resolver I

Re: Calculate the average of a comma separated valuelist

@Beat 

 

I started with your comma delimited list in varList.

UpdateContext({varList:"4,5,6,7"})

 

Then split it into a collection 

ClearCollect(colList,Split(varList,","))


Then take the average of the collection, the default column name of the single column collection is Result
UpdateContext({Average:Average(colList,Result)})

 

as a single expression you can use 

UpdateContext({Average:Average(Split(varList,","),Result)})

 

 

Hope this help!

Mike

View solution in original post

2 REPLIES 2
Highlighted
Resolver I
Resolver I

Re: Calculate the average of a comma separated valuelist

@Beat 

 

I started with your comma delimited list in varList.

UpdateContext({varList:"4,5,6,7"})

 

Then split it into a collection 

ClearCollect(colList,Split(varList,","))


Then take the average of the collection, the default column name of the single column collection is Result
UpdateContext({Average:Average(colList,Result)})

 

as a single expression you can use 

UpdateContext({Average:Average(Split(varList,","),Result)})

 

 

Hope this help!

Mike

View solution in original post

Highlighted
Advocate III
Advocate III

Re: Calculate the average of a comma separated valuelist

@mike528I had no chance to actually try it until today. Happy new year 🙂
It does work, many thanks. I can use this on a form. Is this as well possible in a gallery, for each item? How would I go about that?

Helpful resources

Announcements
Check this Out

Announcing

Class of 2020- Season 2

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,789)