Average based on distinct values in another column

Hey All,

I’m trying to calculate an complete average of my dataset.
I’m trying to calculate the average invoice value. But this value is duplicated because of the activities in a case. How can I calculate this average based on the amount of cases?

I have a column with Case ID and a column with the value,

Example

UID value

1 32

2 5

2 5

4 15

5 33

6 12

6 12

8 5

9 5

10 15

Average should be 15.25

Hello Jasper,

to calculate the average of your table, a Pull-Up function can be used. A Pull-Up function allows you to aggregate a column based on another table. A detailed guide to this type of functions can be found here.

My suggestion would be the following:
AVG( PU_FIRST ( child_table, parent_table.column) )

This will always take the first element of the specified column for each element of the given child table and use that value for the specific case. So for UID 2, the first 5. The single values for each specific case will then be used to calculate the average.

Should you rather want to use the last value, the max. value or the average value of each case to calculate the overall average, you can use the corresponding PU-function for it.

Let me know if this was the answer you were looking for.

Best regards,
Timo