Summarize data in one column by values in the other two

[gvg]

Hi,

This is from accounting. I have a table with Debit and Credit account numbers in different columns and value field in the third column. How do I summarize data to arrive at account balance, please? Account balance is calculated as sum of Credit amounts minus sum of Debit amounts.

My source table looks like this:

    DebitAccount    CreditAccount    Amnt

    123                  456                     100

    322                  456                       50

    456                  789                       70

    456                  322                      200

And the resulting table should look like this:

    Account   Amnt

    123         -100

    322          150

    456         -120

    789            70

Could you please help? I was trying to build a measure with SUMMARIZE, but with no luck.  I know how to build a reference table and make a suitable table for simple summarizing. But my database is huge and I thought that probably making a calculated measure will be more practical.

[Chihiro]

You can unpivot Source table's [DebitAccount] & [CreditAccount]. Then adjust data type as needed.

Rest can be done in DAX.

In Modeling ribbon tool, add "New Table".

TableName = DISTINCT(Source[Account])

Add column:

Amnt =
SUMX (
    FILTER (
        Source,
        Source[Account] = Result[Account]
            && Source[Attribute] = "CreditAccount"
    ),
    [Amnt]
)
    - SUMX (
        FILTER (
            Source,
            Source[Account] = Result[Account]
                && Source[Attribute] = "DebitAccount"
        ),
        [Amnt]
    )

[gvg]

Thanks @Chihiro , but my original table is huge, several gigabytes. No way to build a calculated measure?

[Chihiro · Accepted solution]

Then your best bet may be to do data folding at query stage when you are adding data source (using SQL statements). Assuming you have access to query database.

[gvg]

That's an idea! Thanks.

[MarcelBeug]

A solution in Power Query would be:

let
    Source = Accounting,
    Restructured = Table.FromColumns({Source[DebitAccount]&Source[CreditAccount],List.Transform(Source[Amnt], each _ * -1)&Source[Amnt]},{"Account", "Amnt"}),
    Grouped = Table.Group(Restructured, {"Account"}, {{"Amnt", each List.Sum([Amnt]), type number}})
in
    Grouped

[gvg]

Does this create a new table? It wouldn't work for me as my original table is very big.