Power Platform Community Forum Thread Details

Hi All,

I've done a lot of searching but unfortunately am unable to find a guide explaining the below.

Let's imagine I have two tables:

Table A:

Date	Transaction Type	Amount
14/11/2024	ABC	100.00
13/11/2024	EDF	150.00
12/11/2024	ABC	200.00
11/11/2024	EDF	250.00

I want Table B to look like this:

Transaction Type	Amount
ABC	300.00
EDF	400.00

Column "Amount" in Table B would be simple "SumIf" formula in Excel, however I am having trouble performing this in Dataverse. If possible, I would like if Column "Transaction Type" in Table B could be a type of "lookup" value where it takes all "Transaction Type" values from Table A and lists them as separate rows, without duplication, but if this is not possible, then it is not the end of the world.

Thanks.

Categories:

Microsoft Dataverse with Power Apps

You can use a combination of a Calculated Column or a Rollup Column for the sum, and optionally implement a flow or a view to generate unique "Transaction Type" rows for Table B.

Problem 1 - Sum IF / Roll Up

1 - Use the "Transaction Type" column in Table A as a lookup in Table B.

2 - Create a Rollup Column in Table B:

In Table B, create a new column named Amount and set its data type to Rollup.
Configure the Rollup to:
- Source Table: Table A.
- Filter Criteria: Filter by Transaction Type where the Transaction Type in Table A matches the current row in Table B.
- Aggregation: Sum the Amount field.
The rollup will automatically calculate the sum of the Amount values from Table A for each Transaction Type in Table B.

The roll-up values do not update real time. It depends on your environment setting and typically the default value is one hour. See https://learn.microsoft.com/en-us/power-apps/maker/data-platform/define-rollup-fields

Problem 2 - Dynamically populate Transaction Type in Table B

If the "Transaction Type" rows in Table B need to be dynamically created based on Table A, use a Power Automate flow.

1 - Create an automated flow which triggers when a record is added, modified or deleted in Table A.

2 - Use a List Rows action to retrieve records from Table A.

3 - Use union() expression in Power Automate to obtain distinct Transaction Type (as an array)

4 - For each distinct "Transaction Type", check whether value already exists in Table B. If not, create a new record in Table B

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If this post or my previous reply was useful in other ways, please consider giving it Thumbs Up.