How to calculate AVG

Question & Answer
, , ,
1

How can I calculate the average number of high-risk accounts activated each month in QuickSight? I currently have the distinct count of activated accounts per month using the formula distinct_count(AccountID), but I need to calculate the average across months.

Any guidance would be greatly appreciated!"

Column 1 Column 2 Column 3 Column 4
Month Risk Number Of Accounts Avg
September High 65 ?
September Low 25 ?
August High 55 ?
August Low 15 ?
2

Hi @praveenAWS …

You can create an expression or calculated field like it is shown in the picture
image

and the result is :
image

Let me know if that resolves the issue!

Regards, José Burrull

3

Hi Jose, Thanks for your reply. I am getting this error

Nesting of aggregate functions like avgOver and AVGOVER(SUM(COUNT(DISTINCT “account_uuid”)), “Month”) is not allowed.

4

As far as I Know, in a QuickSight´s expression you can not use 2 aggregation functions together.

5

Hi @praveenAWS

I am assuming your data is at the date level. You can create a calculated field as follows in your analysis

image

Your results should be the following
image

6

Hi @Giridhar.Prabhu … I assumed that @praveenAWS wanted the average by month, independent of the kind of Risk. See us

7

Thanks so much for your help.
I got the answer using this

(countOver(
ifelse({compliance_risk} = ‘High’, {account_uuid}, NULL),
[truncDate(‘MM’, {activation_date})], PRE_AGG
)
/
countOver({account_uuid}, [truncDate(‘MM’, {activation_date})], PRE_AGG)) * 100

Thanks

2 Likes