March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi Experts,
I am stuck in summarizing the totals in a Matrix when I need sum 2 amounts from diferent tables.
Let me explain,
At the beginning i had a table for balances of accounts By period, something like this:
TABLE OF BALANCE:
Lead | Category Code | Category Decription | Class Code | Class Decription | AccountCode | AccountDecription | Period | Balance |
ASSETS | A01A001 | Intercompany | A09-01 | Int Company A | 19920002 | Intercompany - 01 | 2022-01 | 7,133.24 |
ASSETS | A01A001 | Intercompany | A09-01 | Int Company A | 19920003 | Intercompany - 02 | 2022-01 | 2,127.85 |
ASSETS | A01A001 | Intercompany | A09-01 | Int Company A | 19920007 | Intercompany - 03 | 2022-01 | 1,972.63 |
ASSETS | A01A001 | Intercompany | A09-02 | Int Company B | 1992002 | Intercompany - 04 | 2022-01 | 1,846.75 |
ASSETS | A01A001 | Intercompany | A09-02 | Int Company B | 1992002 | Intercompany - 05 | 2022-01 | 1,321.51 |
ASSETS | A01A002 | Dividents | D01-01 | Div Company A | 1992003B | Dividends - 01 | 2022-01 | 1,023.72 |
ASSETS | A01A002 | Dividents | D01-02 | Div Company B | 1992003C | Dividends - 02 | 2022-01 | 815.87 |
ASSETS | A01A002 | Dividents | D01-02 | Div Company B | 19920084 | Dividends - 03 | 2022-01 | 808.43 |
ASSETS | A01A002 | Dividents | D01-02 | Div Company B | 19920116 | Dividends - 04 | 2022-01 | 709.45 |
ASSETS | A01A003 | Cash | C01-01 | Cash Company A | 19920170 | Cash 01 | 2022-01 | 728.83 |
ASSETS | A01A003 | Cash | C01-01 | Cash Company A | 199202 | Cash 02 | 2022-01 | 626.06 |
ASSETS | A01A003 | Cash | C01-01 | Cash Company A | 19920267 | Cash 03 | 2022-01 | 618.72 |
ASSETS | A01A003 | Cash | C01-01 | Cash Company A | 19920294 | Cash 04 | 2022-01 | 541.56 |
ASSETS | A01A003 | Cash | C01-01 | Cash Company A | 199203C | Cash 05 | 2022-01 | 517.99 |
ASSETS | A01A003 | Cash | C01-02 | Cash Company B | 19920701 | Cash 06 | 2022-01 | 485.55 |
ASSETS | A01A003 | Cash | C01-02 | Cash Company B | 19920741 | Cash 07 | 2022-01 | 481.78 |
ASSETS | A01A003 | Cash | C01-02 | Cash Company B | 19920741 | Cash 08 | 2022-01 | 474.96 |
When I build the Matrix, it showed wrong Totals but i created a Measure similar to the below and it was solved.
Now, The user told me that he need have a New Table with manual adjustments by Class code, something like this:
ADJUSTMENT:
Category Code | Category Decription | Period | Amount |
A09-01 | Int Company A | 2022-01 | 1200 |
A09-02 | Int Company B | 2022-01 | -800 |
D01-01 | Div Company A | 2022-01 | 150 |
D01-02 | Div Company B | 2022-01 | 350 |
C01-01 | Cash Company A | 2022-01 | -900 |
C01-02 | Cash Company B | 2022-01 | 850 |
It must added only 1 time by Class Code in the period.
I have not been able to add the amounts of the adjustments in the matrix and generate the correct totals (either they come out duplicates or they come out with wrong values).
I would appreciate it if you could guide me on how I can add the new Adjustments table to the Balances table and obtain the correct totals.
Something like this( I added both amounts in this example only for reference, but the user only need 1 column with the total, he doesn't need show the balance 7 adjustmt amounts)
Lead | Category Code | Category Decription | Class Code | Class Decription | Balance | Adjustment | TOTAL |
ASSETS | A01A001 | Intercompany | A09-01 | Int Company A | 11,233.72 | 1200 | 12,433.72 |
ASSETS | A01A001 | Intercompany | A09-02 | Int Company B | 3,168.26 | -800 | 2,368.26 |
Total Intercompany | 14,401.98 | 400.00 | 14,801.98 | ||||
ASSETS | A01A002 | Dividents | D01-01 | Div Company A | 1,023.72 | 150 | 1,173.72 |
ASSETS | A01A002 | Dividents | D01-02 | Div Company B | 2,333.75 | 350 | 2,683.75 |
Total Dividends | 3,357.47 | 500.00 | 3,857.47 | ||||
ASSETS | A01A003 | Cash | C01-01 | Cash Company A | 3,033.15 | -900 | 2,133.15 |
ASSETS | A01A003 | Cash | C01-02 | Cash Company B | 1,442.28 | 850 | 2,292.28 |
Total Cash | 4,475.43 | (50.00) | 4,425.43 | ||||
Total ASSETS | 22,234.88 | 850.00 | 23,084.88 |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
27 | |
27 | |
21 | |
20 |