Average of category for timeseries

[badger123]

Hello! I'm looking for some help with the following please:

I have a simple table (see below snippet) which I'm using to plot a line chart at a country level, e.g. US - so summing up the values for all the regions. The item_id is linked to another table which has more info on that item, including category (A,B,C,D,E). I would like to create a measure to display another line in my chart, basically the average of the values at each point in time for all items in category A. 

item_id	region_id	date	value
51340821	USAL	2014-12-01 00:00:00	590
51340821	USAK	2014-12-01 00:00:00	90
51340821	USAZ	2014-12-01 00:00:00	1300
51340821	USAR	2014-12-01 00:00:00	260
51340821	USCA	2014-12-01 00:00:00	14800
51340821	USCO	2014-12-01 00:00:00	1000
51340821	USDE	2014-12-01 00:00:00	210
51340821	USDC	2014-12-01 00:00:00	720
51340821	USFL	2014-12-01 00:00:00	4400

Any help would be appreciated greatly!! 🙂 

[v-chuncz-msft · Accepted solution]

@badger123,

You may simply try AVERAGEX and FILTER.

[v-chuncz-msft · Accepted solution]

@badger123,

You may simply try AVERAGEX and FILTER.

[badger123 · Accepted solution]

Thanks, this measure seemed to work:

Average_of_selected_category=
VAR ItemCategory=
SELECTEDVALUE ( 'Table1'[category] )
RETURN
CALCULATE(
AVERAGEX('Table2','Table2'[value]),
FILTER(ALL('Table1'[category]),'Table1'[category]=ItemCategory))