Forum Discussion
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
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] )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
- 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 ) )
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.17Running 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]) )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
- 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
