Matrix Subtotal not adding row totals correctly

[Anonymous]

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.

[v-juanli-msft]

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

Best Regards

Maggie

[Anonymous]

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]

hi @Anonymous  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

[Anonymous]

Oh, I see! Thank you so much! I have been using Chrome and wasn't able to see this! Thanks again! I really appreciate it!

[Anonymous]

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])
)

[v-juanli-msft]

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)

Best Regards

Maggie

[Anonymous]

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]

This simplified data may explain it better.

[Anonymous]

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]

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]
)