Skip to main content

Notifications

Power Apps - Microsoft Dataverse
Suggested answer

"Sum If" of column in one Dataverse Table appearing in column of other Dataverse Table

Posted on 14 Nov 2024 10:40:20 by 11
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:
  • Suggested answer
    Mitanshu Profile Picture
    Mitanshu 1,574 on 26 Nov 2024 at 03:19:17
    "Sum If" of column in one Dataverse Table appearing in column of other Dataverse Table
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November 2024 Newsletter…

November 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #7 Community Profile Tips…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,129

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,797

Leaderboard

Featured topics