Get average of value based on two filters

[Samsam]

Hi there,

I am trying to create a measure that shows the average value of a column based on two filters. Sample dataset is below:

Table Name:	Sample Data
Location	Value	Date
NY	15	31/01/22
NY	12	31/01/22
NY	13	31/01/22
NY	48	25/04/22
NY	16	25/04/22
NY	23	25/04/22
MD	8	16/11/22
MD	54	13/11/22
CA	3	08/08/22
CA	10	08/08/22

I need to get the average value of NY based on the same date. In this case, the desired result should look like this:

Table Name:	Sample Data
Location	Date	Average
NY	31/01/22	13.33
NY	25/04/22	29
MD	16/11/22	8
MD	13/11/22	54
CA	08/08/22	6.5

I've created this DAX measure:

AVERAGEX(
 SUMMARIZE(
  FILTER('Sample Data','Sample Data'[Location]="NY"),
   'Sample Data'[Date],
   'Sample Data'[Value]
),
'Sample Data'[Value]

)

But it's not giving me the desired output. Do I need to group the dates? Please help. Thank you

[FreemanZ]

I am not sure about your use case for the mentioned measure. You could plot the expected table with the existing columns easily like below:

[FreemanZ · Accepted solution]

Hi Samsam, 

I would create a calcualted table as below:

TABLE3 =

VAR TABLE1 =

    SUMMARIZE ( 'Table', 'Table'[Location], 'Table'[Date] )

VAR TABLE2 =

    ADDCOLUMNS ( TABLE1, "Value", CALCULATE ( SUM ( 'Table'[Value] ) ) )

RETURN

    TABLE2

It works and returns below:

[Samsam]

This worked! Thank you so much, FreemanZ. I've been raking my brains out since yesterday to find solution to this. I greatly appreaciate your help. 🙂