Showing results for 
Search instead for 
Did you mean: 

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

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:



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:



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

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

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

Dashboard in a day with date

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!

Power BI Fabric Summit Carousel

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