Calculate the annual average for all the years of the table

[Joaquin_Otero1]

Hi, I’m blocked trying to calculate the annual average of the column [Rain (mm)] for all the years of the table.
So first I need to SUM the Rain of all the rows of each year and make an average of that.
I want to generate a view that shows 1 row with the value of the annual average.
Then I intend to do the same with the month average. But that is different becouse I need to SUM the Rain of all the rows of the actual month for each year and make an average of that.
I want to include in the previous view the historic average of the actual month.

An example would be like this:

Data:

2019 —> 850 mm (the SUM of all the rows for this year)
2020 —> 780 mm (the SUM of all the rows for this year)
2021 —> 800 mm (the SUM of all the rows for this year)

Result I would like to get:

Annual average = 810 mm

---

Data:

March 19 —> 200 mm (the SUM of all the rows for this month and year)
March 20 —> 170 mm (the SUM of all the rows for this month and year)
March 21 —> 150 mm (the SUM of all the rows for this month and year)

Result I would like to get:

Historic average of actual month (March) = 173.33 mm

---

This would be an idea for the Annual average, but is hardcoded and ony for the fixed years included in the formula (in this case 3 years included):

AVERAGE ( LIST ( SUM ( SELECT ( Datos[Diferencia (mm)], YEAR ([Fecha]) = YEAR (TODAY()) )), SUM ( SELECT ( Datos[Diferencia (mm)], YEAR ([Fecha]) = YEAR (TODAY()) -1 )), SUM ( SELECT ( Datos[Diferencia (mm)], YEAR ([Fecha]) = YEAR (TODAY()) -2 )) ))

This would be an idea for the Historic average of actual month, but is hardcoded and ony for the fixed years included in the formula (in this case 3 years included):

AVERAGE ( LIST ( SUM ( SELECT ( Datos[Diferencia (mm)], EOMONTH ([Fecha], 0) = EOMONTH (TODAY(), 0))), SUM ( SELECT ( Datos[Diferencia (mm)], EOMONTH ([Fecha], 0) = EOMONTH (TODAY(), -12))), SUM ( SELECT ( Datos[Diferencia (mm)], EOMONTH ([Fecha], 0) = EOMONTH (TODAY(), -24)))))

Do anyone have an idea of how to do this on an efficient way?

Thanks.

[Koichi_Tsuji]

First you create quick data for the master data for YEAR value. Just one row is enough.

Year

2020
2021
2022
2023
2024
.
.
.
.
Calc

–

Then set this Year field is selected as key, but make sure that this field data type is set to text.

Secondly, on your primal table, you make a virtual column to get the year value from your date field with app formula of

TEXT(YEAR([YourDateField]))

And then set this VC type to REF and reference table is to look up the Year table you just created.

At this point of the step, you make a relationship between year table and your primary table all of your data are stored. This could be confirmed that on your Year table, you will see VC with List type.Each year represented by single row will have the listed associated row out of primary table meaning the YEAR value does match. (usual auto generated REF_ROW() expression )

On this year table you just simly add the virtual column to sum the values per row.

SUM([ListTypeFiled][YourNumericField])

This virtual column will dynamically get the sum for the selected numeric fields for each year.

Make another virtual column to get the total/sum across all the listed year for the table.

For this vc, expression is something like this

IF([Year]=Calc, Sum(Select(YearTable[YourFirstVC], true)), " ")

You see i added one row at the end of year table with value of “Calc” . This expression will get the total sum across the table for this particular row only, else the null value will be shown.

Or even you can make a nother dummy but physical table, jsut ID field only.
On that table, you create VC to get the sum/average whatsoever calculated from the year table. The number will be dynamicaly changed.
Once you create this simple table just for the purpose of the calculation, then you make another column to hold the year table as child table, where you will display year summary as inline (child)

[Joaquin_Otero1]

@tsuji_koichi Thanks for the answer!

I’m at this point:

On this year table you just simly add the virtual column to sum the values per row.

SUM([ListTypeFiled][YourNumericField])

But I got this error message:
Column Name ‘Lluvia por año’ in Schema ‘Años_Schema’ of Column Type ‘Decimal’ has an invalid app formula ‘=SUM([Related Datos][Diferencia (mm)])’. Column ‘Related Datos’ is used in a SELECT or list dereference expression and should be a List/EnumList of Refs

I did everything as you mentioned above.

Thanks.

[Koichi_Tsuji]

Joaquin_Otero1:

Column ‘Related Datos’ is used in a SELECT or list dereference expression and should be a List/EnumList of Refs

This indicates where you are doing something wrong.

[Joaquin_Otero1]

@tsuji_koichi Thanks again,

I don’t see where the problem is.

Numeric field is the following:

[Koichi_Tsuji]

The expression and set ups I can see through the screenshot seems to be fine, but not sure why you get such an error.

[Joaquin_Otero1 · Accepted solution]

I managed to solve this issue in a simple way.

Here goes what I’ve done:

I created two virtual columns, one for Annual average and other for Historic average of actual month.

VC formula for Annual average:

SUM ( SELECT ( Datos[Diferencia (mm)], YEAR ([Fecha]) < YEAR (TODAY()) )) / COUNT ( SELECT ( Datos[Año], AND ( YEAR ([Fecha]) < YEAR (TODAY ()), [Diferencia (mm)] > 0) , TRUE ))

VC formula for Historic average of actual month:

SUM ( SELECT ( Datos[Diferencia (mm)], AND ( MONTH ([Fecha]) = MONTH (TODAY()), YEAR([Fecha]) < YEAR (TODAY()) ))) / COUNT ( SELECT ( Datos[Año], AND ( MONTH ([Fecha]) = MONTH (TODAY ()), YEAR ([Fecha]) < YEAR (TODAY ()), [Diferencia (mm)] > 0), TRUE ))

It is working really good!