cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper IV

## Wrong total in Matrix with two tables

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.

Amount for Matrix = IF(HASONEVALUE(TABLE OF BALANCE[Class Code]),[Balance],SUMX(VALUES(TABLE OF BALANCE[Class Code]),[Balance]))

Now, The user told me that he need have a New Table with manual adjustments by Class code, something like this:

 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
0 REPLIES 0

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors