Monthly average calculation

[miemer]

Hi everyone, i'm looking for help with a power-bi calculation:

I have a table with 4 columns (date, time, Kiln, Paf) of which I want to calculate the monthly average of the PAF column.

Here is an example of a table:

Data	Ora	Forno	Note	PAF
15/03/2019 00:00	1899-12-30 00:00:00	10	Accensione 12/03/2019	29,34
18/03/2019 00:00	1899-12-30 00:00:00	4		16,79
19/03/2019 00:00	1899-12-30 00:00:00	6		12,58
20/03/2019 00:00	1899-12-30 00:00:00	8		20,6
20/03/2019 00:00	1899-12-30 08:15:23	6		12,09
21/03/2019 00:00	1899-12-30 00:00:00	9		7,31
22/03/2019 00:00	1899-12-30 00:00:00	6		7,72
25/03/2019 00:00	1899-12-30 08:00:34	4		3,3
26/03/2019 00:00	1899-12-30 00:00:00	5		6,09
26/03/2019 00:00	1899-12-30 00:00:00	10		18,4
27/03/2019 00:00	1899-12-30 14:00:00	7		7,79
28/03/2019 00:00	1899-12-30 08:00:00	10		12,16
28/03/2019 00:00	1899-12-30 08:00:00	8		14,62
29/03/2019 00:00	1899-12-30 08:30:00	9		7,47
01/04/2019 00:00	1899-12-30 00:00:00	5		2,16
10/04/2019 00:00	1899-12-30 09:22:00	4		9,51
11/04/2019 00:00	1899-12-30 00:00:00	9	prelievo mattina	14,8
15/04/2019 00:00	1899-12-30 14:34:26	8		3,3
16/04/2019 00:00	1899-12-30 14:34:06	6		14,16
17/04/2019 00:00	1899-12-30 14:32:32	7		17,22
19/04/2019 00:00	1899-12-30 11:37:14	10	prelievo h. 8.00	11,52
23/04/2019 00:00	1899-12-30 14:17:19	4		10,42
24/04/2019 00:00	1899-12-30 14:17:50	9		3,32
29/04/2019 00:00	1899-12-30 00:00:00	10		6,53
30/04/2019 00:00	1899-12-30 00:00:00	4	mattina	3,05
30/04/2019 00:00	1899-12-30 08:43:37	8	mattina	5,87
03/05/2019 00:00	1899-12-30 00:00:00	4	mattina	5,96

Thank you for your valuable support

[Anonymous]

Hi buddy,

Try create a measure with this DAX:

Measure = AVERAGE(Table1[PAF])

The trick here is, everytime you use this measure just put the date on the month format from his hierarchy.

Any questions, ask 😉

[miemer]

Thanks, you've been very helpful.

However, I ask you something (maybe) more complex: can I create a column in the table with the monthly average value PAF, so that it is automatically filled in?

Here is the example table I would like to fill in automatically with the average column based on the date, line by line

[Anonymous · Accepted solution]

I couldn't do this way, but i have another solution that might help you.

Create a table

Table = SUMMARIZE(Table1;Table1[Date:])

Create a column on that new table

Column = CALCULATE(AVERAGE(Table1[Values:]);FILTER(Table1;Table1[Date:]='Table'[Date:]))

This should get you this result.

[miemer]

Thank you! exactly what I need