Forum Discussion

AThomasCF's avatar
AThomasCF
Frequent Visitor
2 years ago
Solved

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

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 1Value 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

 

 

Need Help
Tutorial Requests
  • AThomasCF's avatar
    AThomasCF
    2 years ago

    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#])))

3 Replies

    • AThomasCF's avatar
      AThomasCF
      Frequent Visitor
      2 years ago

      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's avatar
        AThomasCF
        Frequent Visitor
        2 years ago

        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#])))