Average By Columns with Filter and Dates

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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]

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".

[daXtreme]

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?

[Anonymous]

I don't know the difference between a measured and calculated column. I actually want the data to be averaged from the past year.

[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.

[Anonymous]

Actually, that would be great if I can have a measured column which uses the inputs on the page (which pick the years/year to include in the plots). How would I be able to get the column in the datatable to account for the filters on the page that is displaying? I didn't think those two things are connected.

[lbendlin]

You cannot calculate columns from filters. That's why you need measures.

[Anonymous]

Yes, that's why I think a measure might be great. Can you show how I would do it? I looked up and saw that you put measures in the reporting/display area. What would the measure equation look like to account for all the columns I showed above? If it is on the page, then I wouldn't need the date filter because the page is already taking care of that. Thanks.

[lbendlin]

Avg = calculate(AVERAGE('Table'[Value]),'Table'[AnalyteType]="Lab",'Table'[DateEntered]>TODAY()-45)

Note that your sample result of "2" is not valid.

[Anonymous]

This doesn't have "Analyte" in the formula, even though we want the final values averaged by analyte (as shown in the "Avg" column in my example.

I tried this equation and it isn't giving the correct values. Thanks.

[Anonymous]

This is the simplest formula. What if I need to add one more column to average by? So not a specific value, but I also want to average by color.

[Anonymous]

I can easily make these all in a table on the report, but then I don't know how to create a plot from a table in the report area. That might be the easiest thing to do. Is that possible?