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

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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.