How to create measures that keep steady value

[Anonymous]

I am working on creating a control chart to show average cost each month in the past 12 months. I have created a Line Chart visual with month on the x-axis and average cost per month on the y-axis. On top of this I need to create measure to the overall average of all cost values in my data table. However, each measure I have created ends up being a line with the exact same values as what is already plotted.

How can I create a value that can be plotted on the line chart that will be the same for every month? Using an average trend line will work, but for the upper and lower control limits this will not. If it is possible to create a measure of the overall average, then in theory, the same DAX forumla can be used by switching the AVERAGE() function with STDEV.S() function.

My data looks like the following:

Month	Year	Total Cost
8	2019	2450
2	2020	2600
3	2019	8745.51
4	2019	6946.67
9	2019	2478.01
10	2019	784.9
2	2020	3237.59
6	2019	2623.99
2	2020	14399.12
9	2019	4273.02
10	2019	922.5

My Line Chart Looks like the following:

Any insights would be appreciated!!

[Anonymous]

Hi @Anonymous ,

Please refer to the measure below.

Measure = CALCULATE(AVERAGE('Table'[Total Cost]),ALLEXCEPT('Table','Table'[ Year],'Table'[Month]))

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[MFelix · Accepted solution]

Hi @Anonymous,

You need to do a measure similar to this

Total average = CALCULATE (AVERAGE(Table[Total Cost]) ; ALL(Table[Month]))