cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User
Super User

Re: DAX expression to show correct Totals

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

JosePintor Frequent Visitor
Frequent Visitor

Re: DAX expression to show correct Totals

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

 

 

Anonymous
Not applicable

Re: DAX expression to show correct Totals

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?

Super User
Super User

Re: DAX expression to show correct Totals

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
Super User
Super User

Re: DAX expression to show correct Totals

@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!


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

JosePintor Frequent Visitor
Frequent Visitor

Re: DAX expression to show correct Totals

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

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 145 members 1,910 guests
Please welcome our newest community members: