Need to average with multiple criteria - Avg(Collect... not working!

lmkane

Hello, I am trying to average scores in a column when criteria is met in two other columns. I know SS does not support AverageIfs, so I used AVG(Collect…. It did return a calculation, but the figure is not correct when compared to a report that has the same data or when I manually calculate the figure.

Formula used

=AVG(COLLECT({Audit - Process Score}, {Project Year}, [Primary Column]@row, {Audit Calc}, "1"))

Data set for 2024 (year is primary column)

Score	Audit	Year
100.00%	1	2024
100.00%	1	2024
80.00%	1	2024
100.00%	1	2024
100.00%	1	2024
80.00%	1	2024

The average of these six scores is 93.33% when calculated manually and on a report I have for a different purpose.

When I use the Avg(Collect… formula the figure returned is 84.29%.

Any idea why there is a discrepancy or how to fix? Thank you!

Brian_Richardson

It's possible you have a mix of numbers and text in the data, and you're matching an unexpected set of rows. By putting a quote around the "1" in your criteria, you're forcing COLLECT to only match text values of 1. My guess is that in your detailed data you might have a mix of numbers and text values.

Try removing the quotes from your COLLECT and see what you get.

If you're in a situation where the column is going to have a mix of text and numbers all the time, you could add a helper column that uses VALUE to extract the number from text so you can match the number every time. Alternatively you can leave the quotes in the COLLECT and add a helper column that adds +"" to the value to make it text every time.

Also, for troubleshooting you can see what numbers are getting matched by replacing AVG with JOIN. That will show you a text list of values that are being collected for averaging, so you can see what may be getting included that you don't expect.

=JOIN(COLLECT({Audit - Process Score}, {Project Year}, [Primary Column]@row, {Audit Calc}, "1")),",")