Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be 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

Reply
gomezc73
Helper IV
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:   

LeadCategory CodeCategory DecriptionClass CodeClass DecriptionAccountCodeAccountDecriptionPeriodBalance
ASSETSA01A001IntercompanyA09-01Int Company A19920002Intercompany - 012022-01  7,133.24
ASSETSA01A001IntercompanyA09-01Int Company A19920003Intercompany - 022022-01  2,127.85
ASSETSA01A001IntercompanyA09-01Int Company A19920007Intercompany - 032022-01  1,972.63
ASSETSA01A001IntercompanyA09-02Int Company B1992002Intercompany - 042022-01  1,846.75
ASSETSA01A001IntercompanyA09-02Int Company B1992002Intercompany - 052022-01  1,321.51
ASSETSA01A002DividentsD01-01Div Company A1992003BDividends - 012022-01  1,023.72
ASSETSA01A002DividentsD01-02Div Company B1992003CDividends - 022022-01     815.87
ASSETSA01A002DividentsD01-02Div Company B19920084Dividends - 032022-01     808.43
ASSETSA01A002DividentsD01-02Div Company B19920116Dividends - 042022-01     709.45
ASSETSA01A003CashC01-01Cash Company A19920170Cash 012022-01     728.83
ASSETSA01A003CashC01-01Cash Company A199202Cash 022022-01     626.06
ASSETSA01A003CashC01-01Cash Company A19920267Cash 032022-01     618.72
ASSETSA01A003CashC01-01Cash Company A19920294Cash 042022-01     541.56
ASSETSA01A003CashC01-01Cash Company A199203CCash 052022-01     517.99
ASSETSA01A003CashC01-02Cash Company B19920701Cash 062022-01     485.55
ASSETSA01A003CashC01-02Cash Company B19920741Cash 072022-01     481.78
ASSETSA01A003CashC01-02Cash Company B19920741Cash 082022-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:

 

   ADJUSTMENT:

Category CodeCategory DecriptionPeriodAmount
A09-01Int Company A2022-011200
A09-02Int Company B2022-01-800
D01-01Div Company A2022-01150
D01-02Div Company B2022-01350
C01-01Cash Company A2022-01-900
C01-02Cash Company B2022-01850

 

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)

  

LeadCategory CodeCategory DecriptionClass CodeClass DecriptionBalanceAdjustmentTOTAL
ASSETSA01A001IntercompanyA09-01Int Company A   11,233.721200  12,433.72
ASSETSA01A001IntercompanyA09-02Int Company B     3,168.26-800    2,368.26
    Total Intercompany   14,401.98    400.00  14,801.98
ASSETSA01A002DividentsD01-01Div Company A     1,023.72150    1,173.72
ASSETSA01A002DividentsD01-02Div Company B     2,333.75350    2,683.75
    Total Dividends     3,357.47    500.00    3,857.47
ASSETSA01A003CashC01-01Cash Company A     3,033.15-900    2,133.15
ASSETSA01A003CashC01-02Cash Company B     1,442.28850    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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.