Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Average By Columns with Filter and Dates

Hello, I have many columns and I would like to average using a filter and certain columns. So I want the "Value" field to be the average by Analyte, but only when AnalyteType = "Lab", and also only f...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi  Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure =
AVERAGEX(FILTER(ALL('Table'),
'Table'[AnalyteType]="Lab"&&'Table'[Analyte]=MAX('Table'[Analyte])&&
'Table'[DateEntered]>=
DATE(YEAR(MAX('Table'[DateEntered]))-1,MONTH(MAX('Table'[DateEntered])),DAY(MAX('Table'[DateEntered])))&&
'Table'[DateEntered]<=MAX('Table'[DateEntered]))
,[Value])

2. Result:

 

Best Regards,

Liu Yang

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    The final column does not look correct. Within each "Analyte", there should be the same value. So "Balloons" should have only the "Lab" values averaged in the final column, and it should only be one value there, just repeated, like how I have it in the example in the column "Average".