How to sum with certain condition

[Yoga_Purnama]

Hi,

I want to sum the item in certain condition, which is item categories. Here is the table for every item purchased in transaction

And here is the table of transaction list

My question for this table is :

How can I sum the “total buying price” for each transaction, but based on category only? for example, in transaction at row 1, the total buying price for category “chemical” would be 225.000.

Let’s say I will make new column to sum the total for each category, but I don’t know the formula or how to sum up the relation column based on the category.

Need help.

Thank you!

[micheal]

Hey Yoga, make a relation that says where Item category is Item category this will make a relation that gets all items in that category.

Then make a rollup pointing to that new relation.

Here’s an example of something similar.

For your use case you would need to categorize each transaction. By row in a table

[nathanaelb]

I think I’d set things up as follows:

• Data architecture. 3 tables: an Items table, an Transactions table, and ItemsTransactions joint table. The items table would not house the transaction ID. The joint table would house the Item ID, Transaction ID, a timestamp, item quantity, any other information needed as part of a transaction.

• Total buying price. In the Transactions table, a query column with the ItemsTransactions table as the source should work fine.

[Jeff_Hager]

@Yoga_Purnama

Let’s say I will make new column to sum the total for each category,

I think You have to go this route based on your current setup.

For each category you will need a Query column pointing to the Item table that checks if the Item name is included in the transaction items, and the category matches the category that the query is being set up for. Create a query column for each category.

Then you can add separate Rollup columns that refer to each query column to sum the amounts.