sum of a remaining balance

[leonyouv]

Hi, I apologize for my bad English.

I've never programmed an app before.

I tried on my own for a very long period without any luck. Any assistance would be much valued!

I want to created tables for an app to know the remain balance for a daily paiment for a work as shown in the tabel below

and I use the following second table, which lists the costs of each service, as a reference:

My challenge is figuring out how to show the total of a customer's daily payments so that the customer's remaining balance may be seen.

Many thanks for any assistance.

Kind regards

[lynchk21 · Accepted solution]

You can use the following steps:

1. Create a column [Cost] in TABLE A that gets the cost for the service
   • Lookup([Service],"Table B","Service","Price")
2. Use a column [Total Costs] with a SUM and SELECT function to total all costs for a client:
   • SUM(SELECT(Table A[Cost],[Num]=[_thisRow].[Num]))
3. Use a column [Total Payments] with a SUM and SELECT function to total all payments for a client:
   • SUM(SELECT(Table A[Daily Payment],[Num]=[_thisRow].[Num]))
4. Subtract the two columns to get the clients balance
   • [Total Costs]-[Total Payments]

You can combine steps as needed. Or you can do this all in Google Sheets with SUMIF functions. You can also use reference columns rather than lookup depending on how your tables are linked. 

[lynchk21]

Can you try to explain again, I am not sure I understand exactly. Thanks!

[leonyouv]

"I'm looking for a simple solution to show how much money each client owes for each service they've requested. For instance, let's say my client John asks for a cleaning service priced at $1000. Today, he pays $200, so I want to display that John still owes $800 for the cleaning service. If he comes back tomorrow and pays $300 more, his remaining balance would be $500, and so on. Also, if possible, I'd like to see the total remaining balance for John if he requests multiple services, like cleaning one day and maintenance another day."

thank you in advanc.

[lynchk21 · Accepted solution]

You can use the following steps:

1. Create a column [Cost] in TABLE A that gets the cost for the service
   • Lookup([Service],"Table B","Service","Price")
2. Use a column [Total Costs] with a SUM and SELECT function to total all costs for a client:
   • SUM(SELECT(Table A[Cost],[Num]=[_thisRow].[Num]))
3. Use a column [Total Payments] with a SUM and SELECT function to total all payments for a client:
   • SUM(SELECT(Table A[Daily Payment],[Num]=[_thisRow].[Num]))
4. Subtract the two columns to get the clients balance
   • [Total Costs]-[Total Payments]

You can combine steps as needed. Or you can do this all in Google Sheets with SUMIF functions. You can also use reference columns rather than lookup depending on how your tables are linked. 

[leonyouv]

"Hi,
Thanks for your response; I really appreciate your help with my problem. I did what you suggested, and it worked perfectly. However, there's a limitation: it only works when a client asks for one service. If a client requests a second or third service, their balance is calculated based on the rate of the first service. So, my question is, how can I calculate each client's balance for each service separately? Thanks again for your help."

[lynchk21 · Accepted solution]

If I understand you correctly, you can add an additional condition to the Select functions that filter down to a single type of service:

• SUM(SELECT(Table A[Cost],AND([Num]=[_thisRow].[Num],[Service]=[_thisRow].[Service])))
• SUM(SELECT(Table A[Daily Payment],AND([Num]=[_thisRow].[Num],[Service]=[_thisRow].[Service])))

[leonyouv]

Great This is precisely what I needed. Thank you for your assistance; I really appreciate it.