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.
Solved! Go to Solution.
Just learned there is average function....
AVERAGE( SELECT( Orders[tip], [cust_id] = [_THISROW].[cust_id])) is a much more readable expression.
You might try below.
Assuming the following..
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.
Just learned there is average function....
AVERAGE( SELECT( Orders[tip], [cust_id] = [_THISROW].[cust_id])) is a much more readable expression.
AVERAGE([Related Orders][Tip])
Even more readable 😉
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?
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 ?
Just to make sure...
Can you try this expression with a double "s" ?
AVERAGE([Related Orderss][Tip])
I tried the expression: AVERAGE([Related Orderss][Tip]) it still gives the same error notification.
Here is my customer table
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.
User | Count |
---|---|
40 | |
32 | |
30 | |
17 | |
16 |