Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Replace null with average across multiple groups


Hi All,


Scenario :


I have a data output with two columns, "group ID" and "work time" - work time is manually filled in and some values are blanks.


What I'd like to do is replace the null values with averages for that group -  as an example:

Group | Time

1         | 5 

1         | 5

1         | [null]

2         | [null]

2         | 10


I'd like the null value to be replaced with the average of the groups.


Can someone explain how I can achieve this? I've experimented with imputation and multi field formula but am a bit stuck.




Alteryx Certified Partner
Alteryx Certified Partner

I'd use a summarize tool and group by the group field and calculate the average (without nulls) for each group.  You can join that back to your data and use a formula like:






Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Alteryx Alumni (Retired)

I always like to communicate the paradigm for solving a data problem in Alteryx is pretty much "as you think through the problem".


In this case, you want to...

  • Get the average for the values that are there (Summarize by group)
  • Filter out the records where the data is Null
  • Replace the Null value with the average (done by a Join)
  • Bring back in the original records that already had data (Union)
  • And then sort back to a group order.


I've attached an example of how this works.

Alteryx Alumni (Retired)

Beat me to it, @MarqueeCrew. :-)

My example is basically the same, but a bit different.