Average Collect Unique Formula


I am looking to pre set a data frame for Tableau Dashboards after form submissions and I need to do an average by a unique Risk Area. First a calculated the average Business Unit score for each Risk Area and now I want to take those average scores for each Business Unit in a Risk Area without duplicated values.

How can I add a part to this formula to only look at the Unique Risk and BU averages and find the average score for the Risk Area?

=IFERROR(AVG(COLLECT([Inherent Risk Score (AVG Sub BU)]:[Inherent Risk Score (AVG Sub BU)], Risk:Risk, =Risk@row)), " ")


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @MJ215

    If I'm understanding you correctly, you only want the Average of the unique values in the column [Inherent Risk Score (AVG Sub BU)]. If so, we can add the DISTINCT function after the AVG but before the COLLECT, like so:

    =IFERROR(AVG(DISTINCT(COLLECT([Inherent Risk Score (AVG Sub BU)]:[Inherent Risk Score (AVG Sub BU)], Risk:Risk, =Risk@row))), " ")

    Let me know if this is what you were looking to do!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!