How to set column to calculate averages of other columns?

(0) Share

Report

Posted on by Usernametwice

503

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.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (1)

EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

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!

Was this reply helpful? Yes No
Usernametwice 503 on at

Like (0)

Report
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!

Was this reply helpful? Yes No
Verified answer

Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

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.

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

Thanks,

Drew

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (1)

Report
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!

Was this reply helpful? Yes No