DAX average formula with filter

[bitbit]

My dataset captures the following:

Country	Version	Month	Classification	Line	Data
China	Actual	Jan	Total	Direct labour headcount
China	Actual	Feb	Total	Direct labour headcount
China	Budget	separate lines for all 12 months	Total	Direct labour headcount
China	Prior Year	separate lines for all 12 months	Total	Direct labour headcount
China	Actual	Jan	Internal	Direct labour headcount
China	Budget	separate lines for all 12 months	Internal	Direct labour headcount
China	Prior Year	separate lines for all 12 months	Internal	Direct labour headcount
China	Actual	etc	Total	S&M headcount
China	Budget	etc	Total	S&M headcount
China	Prior Year	etc	Total	S&M headcount
etc

I tried unsuccessfully to create a DAX formula to calculate the average headcount filtered according to country, month, classification and headcount group:

AvgDL = CALCULATE(AVERAGE('Table'[Data]), FILTER(ALLSELECTED(Table), Table[Line] = "Total direct labour", Table[Classification]="FTE Total",Table[Country],Table[Date] ) ) )

Pls advise how to correct this formula.

[Anonymous]

It looks like you are trying to Average a subset of data and so you should use AVERAGEX formula.

[bitbit]

I did, but still get syntax error.

[Anonymous]

Please share few rows of actual dataset ?

[bitbit]

As requested:

OC	Version	Period	Group	Line	Data
China	Prior Year	1	Total	Total direct manufacturing personnel	76
China	Prior Year	2	Total	Total direct manufacturing personnel	73
China	Prior Year	3	Total	Total direct manufacturing personnel	70
China	Prior Year	4	Total	Total direct manufacturing personnel	67
China	Prior Year	5	Total	Total direct manufacturing personnel	66
China	Prior Year	6	Total	Total direct manufacturing personnel	66
China	Prior Year	7	Total	Total direct manufacturing personnel	67
China	Prior Year	8	Total	Total direct manufacturing personnel	69
China	Prior Year	9	Total	Total direct manufacturing personnel	72
China	Prior Year	10	Total	Total direct manufacturing personnel	80
China	Prior Year	11	Total	Total direct manufacturing personnel	82
China	Prior Year	12	Total	Total direct manufacturing personnel	81
China	Budget	1	Total	Total direct manufacturing personnel	87
China	Budget	2	Total	Total direct manufacturing personnel	81
China	Budget	3	Total	Total direct manufacturing personnel	80
China	Budget	4	Total	Total direct manufacturing personnel	77
China	Budget	5	Total	Total direct manufacturing personnel	75
China	Budget	6	Total	Total direct manufacturing personnel	74
China	Budget	7	Total	Total direct manufacturing personnel	72
China	Budget	8	Total	Total direct manufacturing personnel	75
China	Budget	9	Total	Total direct manufacturing personnel	87
China	Budget	10	Total	Total direct manufacturing personnel	89
China	Budget	11	Total	Total direct manufacturing personnel	91
China	Budget	12	Total	Total direct manufacturing personnel	94
China	Actual	1	Total	Total direct manufacturing personnel	76
China	Actual	2	Total	Total direct manufacturing personnel	68
China	Actual	3	Total	Total direct manufacturing personnel	67
China	Actual	4	Total	Total direct manufacturing personnel	66
China	Actual	5	Total	Total direct manufacturing personnel	63
China	Actual	6	Total	Total direct manufacturing personnel	63
China	Prior Year	1	internal	Total direct manufacturing personnel	58
China	Prior Year	2	internal	Total direct manufacturing personnel	58
China	Prior Year	3	internal	Total direct manufacturing personnel	57
China	Prior Year	4	internal	Total direct manufacturing personnel	57
China	Prior Year	5	internal	Total direct manufacturing personnel	56
China	Prior Year	6	internal	Total direct manufacturing personnel	56
China	Prior Year	7	internal	Total direct manufacturing personnel	56
China	Prior Year	8	internal	Total direct manufacturing personnel	55
China	Prior Year	9	internal	Total direct manufacturing personnel	55
China	Prior Year	10	internal	Total direct manufacturing personnel	55
China	Prior Year	11	internal	Total direct manufacturing personnel	55
China	Prior Year	12	internal	Total direct manufacturing personnel	55
China	Budget	1	internal	Total direct manufacturing personnel	66
China	Budget	2	internal	Total direct manufacturing personnel	66
China	Budget	3	internal	Total direct manufacturing personnel	66
China	Budget	4	internal	Total direct manufacturing personnel	64
China	Budget	5	internal	Total direct manufacturing personnel	65
China	Budget	6	internal	Total direct manufacturing personnel	64
China	Budget	7	internal	Total direct manufacturing personnel	65
China	Budget	8	internal	Total direct manufacturing personnel	65
China	Budget	9	internal	Total direct manufacturing personnel	66
China	Budget	10	internal	Total direct manufacturing personnel	66
China	Budget	11	internal	Total direct manufacturing personnel	69
China	Budget	12	internal	Total direct manufacturing personnel	69
China	Actual	1	internal	Total direct manufacturing personnel	57
China	Actual	2	internal	Total direct manufacturing personnel	56
China	Actual	3	internal	Total direct manufacturing personnel	56
China	Actual	4	internal	Total direct manufacturing personnel	56
China	Actual	5	internal	Total direct manufacturing personnel	55
China	Actual	6	internal	Total direct manufacturing personnel	55

[Ashish_Mathur]

Hi,

There is no Year and/or Date column.  How can one use any Date Intelligence functions on this dataset.

[bitbit]

The "Period" column refers to the month in abbrev.  eg. 1 = Jan.

[Ashish_Mathur]

Hi,

Yes, i see that but there is still no Year column there so i cannot generate a Date column.  It will be ideal if you have a Date column but if you do not then i can still live with 2 seperate columns - one for Year and another for Month.  From these 2 columns, i can generate the Date column.

[Anonymous]

Hi 

Are you looking for Average as below.

Just to make sure if my understanding of your problem is right.

[bitbit]

Yes, the formula should also calculate the average according to the selected month(2).  eg. average for Jan-Mar for actual, budget and prior year.