Average Collect Unique Formula

MJ215

Hi,

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.

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!

Cheers,

Genevieve