cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Beat
Level 8

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
mike528
Level: Powered On

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
mike528
Level: Powered On

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

Beat
Level 8

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
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors
Users online (5,558)