Averaging the Value of Individual Customers Tips

[laderinkomi]

Can I get assistance with the correct expression for the following?

For the column Average Tip in the table Customer Stats.  I am trying to create an expression that will average all related tip values for each individual customer from the column Tip in the table Orders.  

[TeeSee1]

You might try below.

Assuming the following..

• Orders table has [tip], [cust_id] cols
• Cust Stats table has [cust_id], [ave_tip] cols

the AppFormula for [ave_tip] is ... 

SUM( SELECT( Orders[tip],  [cust_id] = [_THISROW].[cust_id]))

/ 

COUNT( FILTER( "Orders",  [cust_id] = [_THISROW].[cust_id]))

Hope this solves your case.

[TeeSee1 · Accepted solution]

Just learned there is average function....

AVERAGE( SELECT( Orders[tip],  [cust_id] = [_THISROW].[cust_id])) is a much more readable expression.

[Aurelien]

AVERAGE([Related Orders][Tip]) 

Even more readable 😉

[laderinkomi]

Thank You, Aurelien.

I get the error message: Unable to find column 'Related Orders'.  Perhaps this is because the 'Related Orders' column is a virtual column.  

Do expressions not work with virtual columns?

[Aurelien]

Hi @laderinkomi 

This is not because of the Virtual/Real property of the columns involved in the expression.

It should the "related ...s" that refers to the orders made by the customer, from the table Customer.

Can you share a screenshot of your Customer's table ?

[Aurelien]

@laderinkomi 

Just to make sure...

Can you try this expression with a double "s" ?

AVERAGE([Related Orderss][Tip]) 

[laderinkomi]

I tried the expression: AVERAGE([Related Orderss][Tip])   it still gives the same error notification.

[laderinkomi]

Here is my customer tableCustomer Table

[laderinkomi]

Thank you so much TeeSee1!

Your expression seemed to do the trick!  I got as far as including the "Select" expression in my equation last night, but didn't think I would have to use "THISROW".  Wow, so grateful for this community.