DAX Measure for calculating the average of monthly totals

[IRJ]

Hi All, I think this is probably a very simple question for the experts but I'm a complete newbie to DAX (and this forum) and would really appreciate your help on this.

A mockup of my data looks like this 

Project	Date	Person	Volume
1	Feb-21	Bill	1.24
2	Feb-21	Bill	0.48
3	Mar-21	Bill	0.06
1	Jul-21	Bill	0.87
2	Jul-21	Bill	0.24
1	Jan-21	Sally	0.23
2	May-21	Sally	0.11
2	Jun-21	Sally	0.45
3	Jun-21	Sally	0.12
1	Jun-21	Fred	0.06
4	Jun-21	Fred	0.77

The monthly sums by person for these data are

Row Labels	Jan	Feb	Mar	May	Jun	Jul
Bill		1.72	0.06			1.11
Sally	0.23			0.11	0.57
Fred					0.83

I'd like to be able to calculate the average of these monthly sums. It should be 0.66. I've tried various permutations of AVERAGE function but have not been able to solve this.  I appreciate it's a basic sort of question and that I have a LOT to learn!

Thanks in advance!

[IRJ]

very helpful replies. thank you

[v-cgao-msft · Accepted solution]

Hi @IRJ ，

Create a new measure and put it in the visual.

The average of monthly totals = 
VAR _total_volume=SUM('Table'[Volume])
VAR _months =DATEDIFF(MIN('Table'[Date]),MAX('Table'[Date]),MONTH)+1
RETURN
_total_volume/_months

The result should be like this.

Attach the pbix file for reference. Hope it helps.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[amitchandak]

@IRJ , You need to try a measure like

AverageX(summarize(Table, Table[Name], Table[Month], "_1", sum(Table[VOlume])), [_1])