Average monthly sales - daily granularity

[campelliann]

Hi, 

So I have the following table "Table1"). And I want to get the average sales from the two months, which should be: 31 (total january) + 28 (total february)/ 2 = 29,5

I did sum(Table1[Sales])/Calculate(Distinctcount[Table1[month]), Table1[sales]>=0) - and it worked.

But is there another way? I tried with the averagex(summarize..) without success (how does one even mention the name of the newly summarized virtual column), or averagex(values month), sum sales)and this is giving me the total... 59
Thanks

Table1 (sales is always 1 to simplify)

Date	Sales	Month
january 1	1	january
january 2	1	january
.....	1	...
February 28	1	February

[Fowmy · Accepted solution]

@campelliann 

You can create a measure as :

Sales average per Month = 
AVERAGEX(
	VALUES('Table1'[Month]),
	CALCULATE(SUM('Table1'[Sales]))
)

[Vera_33]

Hi @campelliann 

If you need SUMMARIZE, you need variable, but performance wise, no need to do it

Just to show you, using GROUPBY, but it is not recommended in this case

avg2 = 
VAR T1 = GROUPBY(Table1,Table1[Month],"SALES", SUMX(CURRENTGROUP(),Table1[Sales]))
RETURN
AVERAGEX(T1,[SALES])

simply do it is quicker

avg = SUM(Table1[Sales])/COUNTROWS(VALUES(Table1[Month]))

[Fowmy · Accepted solution]

@campelliann 

You can create a measure as :

Sales average per Month = 
AVERAGEX(
	VALUES('Table1'[Month]),
	CALCULATE(SUM('Table1'[Sales]))
)

[campelliann · Accepted solution]

@Fowmy , you are brilliant :). The values approach was not working, because I was not using calculate around the sum. You need the calculate to "activate" a sort of filter context row by row, right? 
Other wise I get always the total sum of the table, thus the average being the total.