cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Matrix Subtotal not adding row totals correctly

Hello Everyone.  I'm currently working on a Balance Sheet report that requires a column for Running Total, the sum of all amounts for each account.  The measure works fine as It will give me the correct Running Total per account but the SubTotal for the Running Total is not correctly adding all the rows.  Has any one experience this before?  Any idea what the issue may be and how to resolve?  Any help is greatly appreciated.

 

Running Total = 
IF (
    MIN ( 'Date'[Date] )
        <= CALCULATE ( MAX ( TRANSACTIONS[TRANDATE] ), ALL ( TRANSACTIONS ) ),
    SWITCH (
        CALCULATE ( MAX ( SUBSIDIARIES[PARENT_ID] ), ALL ( TRANSACTIONS ) ),
        3, CALCULATE (
            SUM ( ProfitAndLostUnion[Amount2] ),
            FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ),
            ACCOUNTS[ACCOUNT_ID],
            ACCOUNTING_PERIODS[ACCOUNTING_PERIOD_ID]
        )
            * [Last Current Consolidated FX EUR]
            * [Last Current Consolidated FX],
        CALCULATE (
            SUM ( ProfitAndLostUnion[Amount2] ),
            FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ),
            ACCOUNTS[ACCOUNT_ID],
            ACCOUNTING_PERIODS[ACCOUNTING_PERIOD_ID]
        )
            * [Last Current Consolidated FX]
    )
)


Cash Subtotal of Running Total is not correct.

10 REPLIES 10
Anonymous
Not applicable

Re: Matrix Subtotal not adding row totals correctly

Here are the other two meausres used to calculate the product

 

Last Current Consolidated FX = 
CALCULATE (
    AVERAGE ( CONSOLIDATED_EXCHANGE_RATES[CURRENT_RATE] ),
    FILTER (
        ALLSELECTED ( CONSOLIDATED_EXCHANGE_RATES[Key] ),
        CONSOLIDATED_EXCHANGE_RATES[Key]
            = [Running Key to Consolidated]
    ),
    FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] = MAX ( 'Date'[Date] ) ),
    ACCOUNTS[ACCOUNT_ID],
    ACCOUNTING_PERIODS[ACCOUNTING_PERIOD_ID]
) 
Last Current Consolidated FX EUR = 
CALCULATE (
    AVERAGE ( ConsolidatedExchangeRatesEUROPE[CURRENT_RATE] ),
    FILTER (
        ALLSELECTED ( ConsolidatedExchangeRatesEUROPE[Key] ),
        ConsolidatedExchangeRatesEUROPE[Key]
            = "3^1-" & [Running Period] 
    ),
    FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] = MAX ( 'Date'[Date] ) ),
    ACCOUNTS[ACCOUNT_ID],
    ACCOUNTING_PERIODS[ACCOUNTING_PERIOD_ID]
)
Community Support Team
Community Support Team

Re: Matrix Subtotal not adding row totals correctly

Hi @Anonymous

From your information and screenshot, it is still not clear what's wrong with the sub-running total

 

Based on my understanding, running total should running across the year-month, so please show me more months running total. 

such as

3.png

Best Regards

Maggie

Anonymous
Not applicable

Re: Matrix Subtotal not adding row totals correctly

Hello Maggie @v-juanli-msft That is correct, the Running Total is a calculation across the rows. The running total for individual rows is OK, the issue is the Calculation shows in the Running Total Subtotal. I found a way to separate the way the row Running Total is calculated from the way the Subtotal is calculated. Now I need to figure out how to do a DAX expression that will give me the correct number for Subtotal Running Total. Running Total 2 = IF ( MINX ( 'Date', 'Date'[Date] ) <= CALCULATE ( MAXX ( TRANSACTIONS, TRANSACTIONS[TRANDATE] ), ALL ( TRANSACTIONS ) ), IF ( HASONEFILTER ( ProfitAndLostUnion[Account] ), --ROW TOTAL SWITCH ( CALCULATE ( MAXX ( SUBSIDIARIES, SUBSIDIARIES[PARENT_ID] ), ALL ( TRANSACTIONS ) ), 3, CALCULATE ( SUMX ( ProfitAndLostUnion, ProfitAndLostUnion[Amount2] ), FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= MAXX ( 'Date', 'Date'[Date] ) ) ) * [Last Current Consolidated FX EUR] * [Last Current Consolidated FX], CALCULATE ( SUMX ( ProfitAndLostUnion, ProfitAndLostUnion[Amount2] ), FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= MAXX ( 'Date', 'Date'[Date] ) ) ) * [Last Current Consolidated FX] ), --SUBTOTAL 0 ) )
Anonymous
Not applicable

Re: Matrix Subtotal not adding row totals correctly

Hi @v-juanli-msft, thank you for your reply.  Yes, the Running Total is the total running accross yyyy-mm.

So here is the 3 DAX expressions I have created so far for Running Total.  Not sure what I need to do to get the Subtotal of Running Total to show correctly but the Subtotal Running Total amount should be 3,336,681.57 and not 3,081,312.17



Running Total = 
IF (
    MIN ('Date'[Date] )
        <= CALCULATE ( MAX (TRANSACTIONS[TRANDATE] ), ALL ( TRANSACTIONS ) ),
    SWITCH (
        CALCULATE ( MAX (SUBSIDIARIES[PARENT_ID] ), ALL ( TRANSACTIONS ) ),
        3, CALCULATE (
            SUM (ProfitAndLostUnion[Amount2] ),
            FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= MAX ('Date'[Date] ) ),
            ACCOUNTS[ACCOUNT_ID],
            ACCOUNTING_PERIODS[ACCOUNTING_PERIOD_ID]
        )
            *  [Last Current Consolidated FX EUR]
            *  [Last Current Consolidated FX],
        CALCULATE (
            SUM (ProfitAndLostUnion[Amount2] ),
            FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= MAX ('Date'[Date] ) ),
            ACCOUNTS[ACCOUNT_ID],
            ACCOUNTING_PERIODS[ACCOUNTING_PERIOD_ID]
        )
            * [Last Current Consolidated FX]
    )
)
Running Total 2 = 
VAR RunninbBalancePerRow = SUMX(VALUES(ProfitAndLost[YearMonthNo]),[Running Total])
VAR RunningBalanceSubtotal = SUMMARIZE(ProfitAndLostUnion, ProfitAndLostUnion[YearMonthNo], "RunningTotal", RunninbBalancePerRow)

RETURN
IF (HASONEVALUE(ProfitAndLostUnion[YearMonthNo]),
    RunninbBalancePerRow,
        SUMX(RunningBalanceSubtotal, [RunningTotal])
)
Running Total 3 = 
VAR m_Single= SUMX(VALUES(ProfitAndLost[YearMonthNo]),[Running Total])
VAR m_SubTotal = SUMMARIZE(ProfitAndLostUnion, ProfitAndLostUnion[YearMonthNo], "RunningTotal", m_Single)

RETURN
IF (HASONEVALUE(ProfitAndLostUnion[YearMonthNo]),
    m_Single,
        SUMX(m_SubTotal, [RunningTotal])
)



 

Community Support Team
Community Support Team

Re: Matrix Subtotal not adding row totals correctly

Hi @Anonymous

It seems you missed uploading a picture in your last post, right?

 

the measure "Running Total 2" or "Running Total 3" is a workaround you found untill now,

However, it doesn't work as expected. 

right?

 

based on my test, when add a column which is draged to the "column" field of the matrix in the fuction "HASONEVALUE", it doesn't work, eg. my [Measure],

Measure = IF(HASONEVALUE('calendar'[month]),1,2)

When use the column which is draged to the "row" field of the matrix, it works, eg.my [Measure1]

Measure 2 = IF(HASONEVALUE(Sheet2[sub-cate]),1,0)

3.png

 

Best Regards

Maggie

 

Anonymous
Not applicable

Re: Matrix Subtotal not adding row totals correctly

Thank you @v-juanli-msft
I had tried using the column which is draged to the "row" field of the matrix but that gave me the wrong subtotal also.

My Swith expresion is doing a calculation basedon the MAX(PARENT ID), the values are either 1 or 3.  Since the account on the rows have only 1 or 3 the row amount for running total is calculated correctly.  But since the Subtotal also looks at the MAX (PARENT ID) it will always be value 3.  This is where I have my discrepance as the subtotal should be the calculated total with PARENT ID value 1 added to the calculated total with PARENT ID 3 and not just calculate everything out of value 3.  Makes sence?  I'm now trying to figure out how I'm going to do this.

So far I did this but it seems that from the LAST CURRENT CONSOLIDATED FAX EUR I'm getting the last for the transaction if it was in a previous period and not the last of this period to then do the calculation.   Any ideas?


Running Total USD = 
IF (
    MIN ('Date'[Date] )
        <= CALCULATE ( MAX (TRANSACTIONS[TRANDATE] ), ALL ( TRANSACTIONS ) ),
CALCULATE (
    SUMX ( ProfitAndLostUnion, [Running Total Subsidiary ParentID is 3] ),
    ALL ( ProfitAndLostUnion[YearMonthNo] ),
    FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= MAX ('Date'[Date] ) )
)
    + CALCULATE (
        SUMX ( ProfitAndLostUnion, [Running Total Subsidiary ParentID is not 3] ),
        ALL(ProfitAndLostUnion[YearMonthNo]),
        FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= MAX ('Date'[Date] ) )
    )
)
Running Total Subsidiary ParentID is 3 = 
IF (
    [Parent ID Measure] <> 3,
    0,
    CALCULATE (
        SUMX ( ProfitAndLostUnion, ProfitAndLostUnion[Amount2] ),
        FILTER (
            ALLSELECTED ( 'Date'[Date] ),
            'Date'[Date] <= MAXX ( 'Date', 'Date'[Date] )
        )
    )
        * CALCULATE ( [Last Current Consolidated FX EUR], 
            FILTER ( 
                ALLSELECTED ( 
                    'Date'[Date] ), 
                    'Date'[Date] = MAX ( 'Date'[Date] ) 
                    )
        )
        * CALCULATE ( [Last Current Consolidated FX], 
            FILTER ( 
                ALLSELECTED ( 
                    'Date'[Date] ), 
                    'Date'[Date] = MAX ( 'Date'[Date] ) 
                    )
        )
)

 

Running Total Subsidiary ParentID is not 3 = 

IF([Parent ID Measure] = 3,0,
CALCULATE (
                SUMX (ProfitAndLostUnion, ProfitAndLostUnion[Amount2] ),
                FILTER (
                    ALLSELECTED ( 'Date'[Date] ),
                    'Date'[Date] <= MAXX ( 'Date', 'Date'[Date] )
                )
            )
    )
        * [Last Current Consolidated FX]




 

Anonymous
Not applicable

Re: Matrix Subtotal not adding row totals correctly

This simplified data may explain it better.
Capture.PNG

hahsyaj Frequent Visitor
Frequent Visitor

Re: Matrix Subtotal not adding row totals correctly

Hi, I am very new to the Community. It might sound stupid, but how did you manage to post a screenshot of your data? I have been trying to create a message and captured my data using Snipping tool, but I am not able to Paste that capture in the Message Body. Also, I don't see any "Attach" option. Sorry for the inconvenience. Thanks, J
Anonymous
Not applicable

Re: Matrix Subtotal not adding row totals correctly

hi @hahsyaj  There is an icon that looks like a camera and says photo.  But make sure you are using Internet Explorer and note that verion 10 of IE is no longer supported

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 127 members 1,522 guests
Please welcome our newest community members: