Forum Discussion
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 for dates within the past 1.5 months (ideally, I want it to only use the last 12 months, but I didn't make a large enough sample dataset here).
I was trying to use the ALLEXCEPT, but I couldn't figure out how to select the AnalyteType = "Lab" nor could I figure out how to keep the date updated everytime I reload the data (which will happen once per week).
| AnalyteType | Analyte | Value | DateEntered | Average |
| Lab | Balloons | 5 | 12-02-2022 | 5 |
| Field | Balloons | 6 | 12-03-2022 | 5 |
| Outside | Balloons | 4 | 12-04-2022 | 5 |
| Lab | Faces | 2 | 11-05-2021 | 2 |
| Field | Faces | 3 | 11-06-2021 | 2 |
| Outside | Faces | 1 | 11-07-2021 | 2 |
| Lab | Chairs | 11 | 10-01-2021 | 15 |
| Field | Chairs | 12 | 10-02-2021 | 15 |
| Outside | Chairs | 17 | 10-03-2021 | 15 |
| Lab | Chairs | 15 | 12-02-2022 | 15 |
| Field | Chairs | 12 | 12-03-2022 | 15 |
| Outside | Chairs | 18 | 12-04-2022 | 15 |
14 Replies
- lbendlin
Super User
Avg = calculate(AVERAGE('Table'[Value]),'Table'[AnalyteType]="Lab",'Table'[DateEntered]>TODAY()-45)Note that your sample result of "2" is not valid.
daXtremeSolution Sage
Are you talking about a measure or a calculated column? Also, what does it mean "only for dates within the past 1.5 months"? For a group that you'd use for the calc of average, there may be many different dates. Which dates are you then talking about for a group of rows? And what happens if at least one of the rows in the group that you'd like to calc the average for has a date that's not within 1.5 months from now?
- lbendlin
calculated column: is computed once during dataset refresh, and stored as part of the data model. Is not impacted by user choices.
measure: is computed on the fly whenever the user interacts with the report in a way that includes that measure. Is not stored in the data model. Is impacted by user choices/filters.
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
For the final plot, I would like a clustered column chart, but since each Analyte might have really different scales, the plot would be too hard to see if the "lab" value is any different than the "outside" value. So I want to scale everything so the lab value is set to 100% and the "outside" and "field" values then show the percent difference compared with the "lab" values. So if I have a column of the average of "lab" values, then I can divide the actual values by that column and create the plot based on averages by "AnalyteType".
In the "table" function of the report, I can create averages by AnalyteType with the Standard Deviation as well, even applying the filter of which years to select from the page. I just don't know how to translate that to a plot. I would still need to divide the average by the "lab" average though.
I was able to get this working. I created two columns. One column has the "Lab" averages, and one has the group average, then I divide the group by the lab to get the scaled difference. Here is the code I used to create the "lab" average:
Column1 = calculate(average(Main[Value]),filter(Main,Main[Analyte]=EARLIER(Main[Analyte])),filter(Main,Main[Color]=EARLIER(Main[Color])),filter(Main,Main[AnalyteType]="Lab"),filter(Main,Main[DateEnter]>today()-365))Thanks a bunch for your help.
