Forum Discussion

sammy3's avatar
sammy3
New Member
5 years ago
Solved

Creating a column average based on two other columns

Hello,   I need some assistance in finding a solution to this problem. I want to create a column that would display the average of [Value] based on [ID] if the [Location] column has common values. ...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi sammy3 ,

     

    Create a measure as below:

     

     

    Measure = CALCULATE(AVERAGE('Table'[value]),FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])&&'Table'[Location]=MAX('Table'[Location])))

     

     

    Or you could create a calculated column as below if needed :

     

     

    Column = CALCULATE(AVERAGE('Table'[value]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Location]=EARLIER('Table'[Location])))

     

     

    And you will see:

     

     

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

v-kelly-msft's avatar
v-kelly-msft
Community Support
5 years ago

Hi sammy3 ,

 

Create a measure as below:

 

 

Measure = CALCULATE(AVERAGE('Table'[value]),FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])&&'Table'[Location]=MAX('Table'[Location])))

 

 

Or you could create a calculated column as below if needed :

 

 

Column = CALCULATE(AVERAGE('Table'[value]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Location]=EARLIER('Table'[Location])))

 

 

And you will see:

 

 

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

SuzanneNZ's avatar
SuzanneNZ
Frequent Visitor
3 years ago

This works perfectly but is then impacted by my page filters.  If I add the column in the Power Query Filter I assume the filters added later in the desktop will not change the value but I'm struggling to translate to DAX