Help needed - measures/matrix

[Petrx]

Hello,

Please, I am looking for help with creating of matrix from general ledger (accounting).

- I have some accounts used in the general ledger (data source table) where one transaction equals one line where both debit and credit column is used in this line (with different accounts) and the same amount is used for both sides (debit and credit columns at each line).

- Each account can be used both at debit and credit side,

- Amounts can be with both positive and negative sign both at debit or credit side,

- I need to create a matrix where I will have one column with account number (at lines each account appears only once), and sum of debits (column) and sum of credits (column) and the difference between Debit and Credit columns (all for each account used), all to be filtered by date  (UMD = debit, UD = credit, Kc = amount valid for both UMD and UD account).

- The source look like this (first column is debit, second column is credit, third column is amount).

The output shall be:

Account; sum of amounts at debit side; sum of amounts at credit side; difference between sum of amounts.

Can you please help?

Thank you

Petr

[v-yalanwu-msft · Accepted solution]

Hi, @Petrx ;

You could unpivot column in power query ,then create a measure .

1. unpivot in power query.（Selected UMD and UD column then unpivot）

2. create a measure.

Measure = IF(ISINSCOPE('Table'[kind]),SUM([Kc]),SUMX(FILTER('Table',[kind]="UMD"),[Kc])-SUMX(FILTER('Table',[kind]="UD"),[Kc]))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aj1973]

Hi @Petrx 

When dealing with GL(General Ledger) You need to start grouping the Accounts numbers then use DAX to sum up those accounts Debit & Credit.

Check this out

Microsoft Power BI