Average of Sum

[rakshabhat]

Hello team,

I have a question regarding summarize tool.

I am working on a topic which requires me to calculate Average of sums.

I group the data by certain number of fields. I calculate sum of one field, for example : Spend.

In the next step, I need the average of this sum.

For this, if I add another summarise tool and select average of the newly created sum, alteryx just gives me the sum. 

How do I get average of sum in alteryx?

In this example, it must be 19875 (as shown in excel)

Attached workflow, alteryx database, excel for your reference.

Thanks in advance. 

Raksha

[jvansistine · Accepted solution]

Hi @rakshabhat,

It looks like you are taking the average by both City and State in the second Summarize tool, which is the level you're already summarized to.  So, with just 1 row per city/state combination the average for the same city/state combination equals the sum.

If you want the average of across the cities, try doing the average step grouped only by state, then joining those records back to your City/State sums.  See attached example.

Jim

[rakshabhat]

Hi Jim,

Thank you so much, it works. I really appreciate your time.😊

Raksha