Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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
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]) )
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
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]
This simplified data may explain it better.
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] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |