I'm try to generating Average Values based by location code and graph the results

[AThomasCF]

Hello there. The upshot is that we send product out for testing. We are trying to trend out what values we get from each location so that we have general ideas what we are getting.

Here is a model of the data that I am working with

Location #	Value 1	Value 2
282	90	9
282	140	9
291	50	9
291	60	10
291	12,900	10
454	536,000	100
454	27,500	900
454	583,000	200
454	690,000	40

I am looking for a way to return the average values from the tests of sites 282, 291 and 454

I tried to use AverageX as a measure to get the result but there is an unknown error in my syntax that I can't nail down and I am unsure if that is the best way to go about it.

example: Average Value 1 = AVERAGEX(VALUES('Table'[Location#]), 'Table'[Value 1]) 

For bonus point, a way to add a error bars

[amitchandak]

@AThomasCF , A new measure Avg of sum

AVERAGEX(VALUES('Table'[Location#]),calculate(Sum( 'Table'[Value 1]) ))

Avg of Sum : https://youtu.be/cN8AO3_vmlY?t=22980

[AThomasCF]

Hmm, your method is fantastic for getting the values as they should be, but I am seeing amounts on the Y axis that are way higher than any individual value for that code. Am I accidentally getting the sum instead of the average?

[AThomasCF · Accepted solution]

After some tinkering, I figured out what was wrong: I was getting a pure sum of all values without an average. I needed to divide the sum by the count of each time the location appeared and that got what I had calculated previously using Excel

Here is my final code:

= AVERAGEX(VALUES('Table'[Location#]),CALCULATE(SUM('Data'[Value 1])/COUNT('Table'[Location#])))