Average over entire column in DAX measure when filtered

[Anonymous]

Hi,

I am using a "Line and clustered column chart" and would like to add an additional line to dsiplay the average value of one of the columns. I have written a DAX measure that pefectly calculates me the value that I want to see and I can use it on cards etc. However as soon as I put the measure on the chart, it is being calculated for each category on the x-axis again. Here is the structure of the measure that I use - it sums up values in a table (Table1) based on a filter in another table (Table2) and then divides by the number of selected entries in Table2.:

avg_activity = 
VAR sumofactivity = 
CALCULATE(
	SUM('Table1'[Activity]);
	'Table2'[Type] IN { "mytype" }
)
RETURN
VAR countofactivity =
CALCULATE(COUNTROWS(Table2);'Table2'[Type] IN { "mytype" })
RETURN
sumofactivity/countofactivity

The month year hierachy on the x-axis is also from Table2, the values on the chart are from Table1. Now what I would like to get when putting this measure on a the chart wozuld be something like this: 

but what I get looks like this:

I've also tried using caluclated columns but with no success. I know that the problem is me using the Calculate-Function wrong but would be very thankful for any hint on how to get the desired behaviour!

[Greg_Deckler]

Perhaps:

avg_activity = 
VAR sumofactivity = 
CALCULATE(
	SUMX(ALL('Table1'),[Activity]);
	'Table2'[Type] IN { "mytype" }
)
RETURN
VAR countofactivity =
CALCULATE(COUNTROWS(ALL(Table2));'Table2'[Type] IN { "mytype" })
RETURN
sumofactivity/countofactivity

[Anonymous]

Hi Greg, thanks for your quick response! Unfortunately that creates a value which is way too high, however at least it displays now a constant line, so I think you showed me the right track.