cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Usernametwice23
Helper V
Helper V

How to set column to calculate averages of other columns?

Hi all 

 

I'm trying to create a calculated field so that it calculates the averages of 6 other columns when a new row is created. I know in share point I can use - 

=AVERAGE([value],[value],[value],[value],[value],[value])

But when i try in dataverse i get 'The formula contains invalid characters'. 

 

Is there a way to do this? I need it for my datatable in powerapps as I want to create a new column that shows the average for each row. 

 

Thanks for your help. 

1 ACCEPTED SOLUTION

Accepted Solutions
dpoggemann
Super User
Super User

Hi @Usernametwice23 ,

 

A simple way to accomplish this in the Dataverse is to do what the Average Function does and manually create a calculated field to store the average.  Note, the fields need to be all same time, like Decimal to accomplish to get the correct.

 

In example below I added three Decimal fields Value 1 - 3 and then a Value A which is Calculated as the average of the fields.

Screen Shot 2021-10-25 at 5.07.33 AM.pngScreen Shot 2021-10-25 at 5.11.27 AM.png

 

Hope this helps.  Please accept if answers your question or Like if helps in any way.


Thanks,


Drew

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

View solution in original post

4 REPLIES 4
EricRegnier
Super User
Super User

HI @Usernametwice23,

Unfortunately up to now it's not possible with Calculated fields/columns. Watch out for this space though it's coming very soon and you'll be able to do it with PowerFx. More info: https://powerapps.microsoft.com/blog/power-fx-coming-to-model-driven-power-apps-dataverse-and-more/

In the meatime, you can calculate the average with code using JavaScript on the forms or plugins

Hope this helps!

Usernametwice23
Helper V
Helper V

Hi @EricRegnier  Thanks for your help. 

 

Okay I did not know we could write javascript inside powerapps. Would I need to begin by writing it on the data table 'items'? And just add in a script? 

 

Currently inside my data table 'items' I have a filter - 

SortByColumns(Filter('Daily Measurement ', 'Daily Measurement (Views)'.'Active Daily Measurement Attributes','Value'.'Value'=Gallery.Selected.'Value'.'Value'),"334_number",Ascending)

 

So would I begin writing the script under this filter? 

 

Thanks again!

dpoggemann
Super User
Super User

Hi @Usernametwice23 ,

 

A simple way to accomplish this in the Dataverse is to do what the Average Function does and manually create a calculated field to store the average.  Note, the fields need to be all same time, like Decimal to accomplish to get the correct.

 

In example below I added three Decimal fields Value 1 - 3 and then a Value A which is Calculated as the average of the fields.

Screen Shot 2021-10-25 at 5.07.33 AM.pngScreen Shot 2021-10-25 at 5.11.27 AM.png

 

Hope this helps.  Please accept if answers your question or Like if helps in any way.


Thanks,


Drew

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

View solution in original post

Hi @Usernametwice23, based on your reply, it looks like you're using canvas apps, not model-driven apps. JS is not supported in canvas , only in model-driven apps. Since your using canvas app you can run the AVERAGE function directly. Here's an exmaple:

AddColumn(
 SortByColumns(Filter('Daily Measurement ', 'Daily Measurement (Views)'.'Active Daily Measurement Attributes','Value'.'Value'=Gallery.Selected.'Value'.'Value'),"334_number",Ascending)
, "Average"
, Average(Value1, Value2, Value3))

 Hope this helps!

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Users online (1,559)