Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX expression to show correct Totals

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?



Screenshot (13).png

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

 

6 REPLIES 6
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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:

 

https://community.powerbi.com/t5/Desktop/MATRIX-GrandTotal-calculation-with-TOPN-multiples-dimension...

 

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!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

 

Anonymous
Not applicable

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.