How to write this expression - sum based on Customer number/amount

AppSheet AppSheet Q&A
1

I need an expression to help me sum customer transactions. In my table i have a list of customers. They have a unique id (Customer Number). The table shows all transactions done that month PER customer. I need a virtual column that sums the transactions per customer.

2X_5_5a586dc0d63d889f78962b9bb6f6e99576b3cfeb.png
2X_5_5a586dc0d63d889f78962b9bb6f6e99576b3cfeb.png1285×341 15.4 KB

Then i need to reference another table that shows the same Customers, customer number - their balance. I need to calculate (while referencing between these tables):

2X_4_4ce72e89fd08026c07538479c9bc2bdfca7a7282.png
2X_4_4ce72e89fd08026c07538479c9bc2bdfca7a7282.png749×578 43.3 KB

Customer Balance / Total of transactions (from the first table i need to total) - PER CUSTOMER

2

More information please.

3

I have been given two tables. In table 1,I have customer balances. In table 2 i have the same customers - but i have individual transactions (invoices for the month).

I need to reference these two tables (as i need the balance from table 1 and total of transactions from table 2)

Calculation i am trying to get to:

Customer Balance (from Table 1) / Customer Total transactions for that month (From table 2 - that i still need to calculate)

4

A VC in Table 1 like…

[Customer Balance]/SUM(SELECT(Table2[Final Trx Amount], [_THISROW].[Customer Name]=[Customer Name]))

5

Ok. This seemed to work PERFECT! Thank you very much.

1 Like
6

Ok. I was told that per customer is not what they want. I need:

Total Balances (from table 1) /
Total Transactions (table 2)

Sorry- i was told to do this per customer - now they changed it…

7

In that case, I would create a third table with only one Text column and one VC.

In the VC:
SUM(Table1[Customer Balance])/SUM(Table2[Final Trx Amount])
The text column you can do whatever with. Maybe a description of what the final number means or something.

8

Ok. Let me try this. Thank you again