Calculating Average and Sum in one DAX measure based on column value

[Anonymous]

Hi All,

Appreciaet your help as I am trying to have one DAX measures which will calculate average or sum based on another column value. for headcount i need average for last two months and for sales and profit i need sum for last two months. 
how can i have measure which will give em the values given in the Expected results columns.

Thanks in advance,

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood correctly your question, but please check the below picture and the attached pbix file.

Expected result: =

VAR currentmetrictable =

FILTER ( ALL ( Data ), Data[Metric] = MAX ( Data[Metric] ) )

RETURN

SWITCH (

TRUE (),

SELECTEDVALUE ( Data[Index] ) = 7

&& SELECTEDVALUE ( 'Month'[Index] ) = 7,

IF (

SELECTEDVALUE ( Data[Metric] ) = "Head count",

AVERAGEX ( currentmetrictable, Data[Values] ),

SUMX ( currentmetrictable, Data[Values] )

)

)

[Anonymous]

Hi @Jihwan_Kim Thanks this really helped where I have dates filter on the visual and I will accept as  a solution.
What if (in another scenraio) I want to ignore the dates/date table reference in the Expected result measure and rather want to have an average values where metric is headcount and I want sum of Values where metric on the row is Sales and Profit? I want to get the values in the expected result column by using VAR in DAX. Please help, example is given below.