cancel
Showing results for
Did you mean:
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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

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.

Thanks,

Drew

Thanks,
Drew
4 REPLIES 4
Super User

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!

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!

Super User

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.

Thanks,

Drew

Thanks,
Drew
Super User

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:

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!

Announcements

#### Calling all User Group Leaders!

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

#### Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,559)