Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Everyone
From below screenshot all accounts have the correct Amounts. Yet for the Running Total and Running Total 1, I'm not able to get the correct Total. Any idea what I may be doing wrong?
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 1 = IF ( MINX ( 'Date', 'Date'[Date] ) <= CALCULATE ( MAXX ( TRANSACTIONS, TRANSACTIONS[TRANDATE] ), ALL ( TRANSACTIONS ) ), IF ( HASONEFILTER ( ProfitAndLostUnion[Account] ), --ROW TOTAL SWITCH ( CALCULATE ( MAXX ( ProfitAndLostUnion, VALUE ( ProfitAndLostUnion[Subsidiary 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 IF ( MINX ( 'Date', 'Date'[Date] ) <= CALCULATE ( MAXX ( TRANSACTIONS, TRANSACTIONS[TRANDATE] ), ALL ( TRANSACTIONS ) ), CALCULATE ( SUMX ( FILTER ( ProfitAndLostUnion, ProfitAndLostUnion[Subsidiary Parent ID] = "3" ), ProfitAndLostUnion[Amount2] * [Last Current Consolidated FX EUR] * [Last Current Consolidated FX] ), FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= MAXX ( 'Date', 'Date'[Date] ) ) ) + CALCULATE ( SUMX ( FILTER ( ProfitAndLostUnion, ProfitAndLostUnion[Subsidiary Parent ID] <> "3" ), ProfitAndLostUnion[Amount2] * [Last Current Consolidated FX] ), FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= MAXX ( 'Date', 'Date'[Date] ) ) ) ) ) )
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] ) ), USERELATIONSHIP(ProfitAndLostUnion[FX Key],CONSOLIDATED_EXCHANGE_RATES[Key]) )
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] ) ), USERELATIONSHIP(ProfitAndLostUnion[FX Key],ConsolidatedExchangeRatesEUROPE[Key]) )
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Hi Greg,
I have a similar issue with totals calculation with TOPN measures for hierarchy matrix, but with the links you provide here, i can't figure out how to solve it.
Can you take a look on this link please:
Thanks,
José Pintor
@JosePintor - Took a look at the thread, you have @MattAllington on the case and I agree with his analysis. You have a very complex model that it seems you are trying to wrangle via DAX and in my experience that's an uphill battle. Much better to simplify the model by doing some work on the Power Query side of things. DAX is powerful but it can't cure all the ills of an overly complex model. And besides, @MattAllington literally wrote the book (one of them anyway) on DAX so you're in good hands!
Hi Greg,
Thanks for anwser,
I (re)published the model now with only 6 tables, and now it's easier to take a look.
If you want and have a few minutes, i will appreciate too.
Thanks
José Pintor
Thanks for your prompt reply @Greg_Deckler
I actualy read both of those posts last week and attempted to solve the issue but without success. I'm just frustrated at this point not been able to move forward with the report I was building. Insnt the solution you provided what I did for measure Running Total 1?
OK, I don't know exactly what your source data looks but what I would expect for "Running Total 1" (assuming that [Running Total] is returning the correct value for everything except the Total line would be something like:
Running Total 1 = VAR __table = SUMMARIZE('ProfitAndLostUnion',[Type],[Category],[Sub Category],[Account],[Subsidiary Parent ID],"__runningTotal",[Running Total]) IF ( HASONEVALUE('Table'[Account]), [Running Total], SUMX(__table,[__runningTotal]) )
So basically you are creating a temporary table in memory that is summarized exactly like how you are displaying the table visual. Since the measure you created is correct for each individual row, then it will be correct for each individual row in your temp table. So, then you can just SUMX down that column and you should have your correct answer.
Hoping that helps and that I didn't miss anything.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |