Generating separate sums for each selection from enum column... please help!

[michael12]

Hello, this is my first post. I’m new to AppSheet and am having a hard time finding a solution to this:

User creates a Monthly Expense Report with the following steps:

(1) User inputs the date/name of expense

(1) User can choose from a list of expense types, picking only one type per expense (Enum Column)

(2) He/she can then specify how much money came from each of two types of accounts (separate Price columns).

I have been able to generate sums for each Money Source. (From example above, Total from Money source 1 = $360). I also can generate overall sum ($480).

I am stumped trying to generate a sum for the specific expense type (as chosen from the Enum dropdown). For example: I want to be able to generate the sum of the Lodging expense type from Money Source 1 ($310).

Can someone please help me out with the formula I would use? In case it’s important the list of values for the Enum column are chosen within the column options (not referenced from another table). Thanks so much in advance…

[Rafael_ANEIC-PY]

Hello @michael1, welcome to our community !

There are various different ways to solve your problem, depending if…

1. You have a table that lists your expense types, which are then saved as ref values on each new expense
2. Or they’re just pre defined text inside their enum column.

If your case is the number 1, then you I suggest you create a new table (inside your template I mean) and check out template Start Expressions, as these will allow you to list all your expense types in your template, then it is only a matter of making the expression to sum each type.

If your case is the number 2, then you must code by hand each instance of expense type that you wish to sum, for this, you’re gonna need a SELECT(), which is used to extract a group of rows from a table, which then you can sum using SUM()

In general, if you wish to obtain an exact expression as a solution to your query in this forum, it is necessary that you share your column structure related to the tables of interest (meaning, the name of your columns)

[michael12]

Thanks so much for your detailed response.

I think your answer is probably sufficient but I am an absolute newbie. I’m not trying to just be a taker but am just learning this for the first time…

I am trying to have the exact formulas to generate a report like this:

As you can see, I was able to get the formula to work to generate subtotals by money source (e.g. Portion Paid By Bank 1 and Portion Paid By Bank 2). I also can generate the Total.

But, I can’t figure out how to generate the running subtotal by category (in the report image above I have $7 for the category Transportation which is also Paid For By Bank 1.

I can’t figure out generate a formula for that. Any help would be so appreciated (I’m 8 hours deep trying to solve this one issue!)

In case you can’t see the app here are a bunch of screenshots with hopefully enough detail:

Uploading: Screen Shot 2022-01-18 at 7.24.02 AM.png…
Uploading: Screen Shot 2022-01-18 at 7.23.54 AM.png…

Also, in case not clear. The list of categories in the table Expense Type looks like this:

[Steve · Accepted solution]

To get the sum of Portion Paid By Bank 1 for the Transportation category for the Trip ID of this row of the in the Trip table:

SUM(
  SELECT(
    Expense[Portion Paid By Bank 1],
    AND(
      ([_THISROW].[Trip ID] = [Trip ID]),
      ("Transportation" = [Expense Type])
    )
  )
)

[michael12]

Thanks so much Steve, I appreciate your help.