Average data by a Category (region) and a date

[Anonymous]

Hello all,

I've been looking over the forums for a bit today to try and figure this out but haven't found something that quite lines up.  

THis post is close but no cigar.

https://community.powerbi.com/t5/Desktop/Average-by-category-AND-date/m-p/683199#M329137

I want to get an average monthly headcount (over the past year, rolling 12 not calendar) by Region.  Each calculation I've tried to do this results in an average for each location, not it's region.  When I pull the data into a table and I just have Region and Headcount it still just averages by location.  

I want to take the results of this and feed this into another calculation.

I have data that looks like below:

Date	Location	Region	Headcount
1/1/2019	a	North	10
1/1/2019	a	South	30
1/1/2019	b	North	15
1/1/2019	b	South	100
2/1/2019	a	North	15
2/1/2019	a	South	40
2/1/2019	b	North	20
2/1/2019	b	South	78
3/1/2019	a	North	30
3/1/2019	a	South	23
3/1/2019	b	North	90
3/1/2019	b	South	14

Now I would like to Group and Average like below:

North
Jan	25
Feb	35	Monthly Average
March	120	60
South		Monthly Average
Jan	130	95
Feb	118
March	37

[Anonymous · Accepted solution]

Hi @Anonymous ,

Create a new column

YearMonth = (YEAR(TableName[Date])*100)+MONTH(TableName[Date])

Then Create this measure

Average HC by Region = DIVIDE(

SUM(TableName[Headcount]),

DISTINCTCOUNT(TableName[YearMonth])

)

You can display this measure in table with Region.