Help with nested SUMX with calculations based on SUMX Context

[ghaines]

I am aware this is a long post.  In short, how Do you read the context/current value of a SUMX in order to calculate/lookup/adjust the result of the expression within the SUMX, especially with nested SUMX?

e.g.

SUMX(VALUES(Table1[Column1]),
    SUMX(VALUES(Table2[Column2]),
        SELECTEDVALUE(Table1[Column1]) * SELECTEDVALUE(Table2[Column2])
    )
)

Full description:

I am trying to calculate sales in a representative local currency.  My main fact table is 'PnL Transaction Lines'.  The currency of transaction is recorded in the column 'PnL Transaction Lines'[INVOICE_CURRENCY].  There are dimension tables of Country, Subsidiary, Date_Calendar (A date table).

There is another table which has the monthly average exchange rates for the base currencies of each subsidiary.

I am trying to calculate all sales converted to the country's main currency.  For example a subsidiary that encompasses all of Western Europe could have its subsidiary base currency as EUR, but each country have a different Local Currency it uses for reporting, with invoices in a mix of currencies.  I have the price of sales in both the local currency, and the subsidiary currency based on a spot rate.

I want to calculate

(All revenue for the country that is in the correct currency already (SUM(local price)) + 

(All revenue not in the correct currency already converted (SUMX(subsidiary price * XR), evaluated across country currency, subsidiary currency, month)

The second part is not working, specifically the exchange rate part.

How can I read the current values of my dimension tables within my evaluation of the correct exchange rate?

Current attempt:

SalesRep_DevRev_TransformLoc_Net = 
SUMX(VALUES(Country[Local Currency]),

    VAR CountryCurrency = SELECTEDVALUE(Country[Local Currency])

    RETURN
    CALCULATE([SalesRep_DevRev_Loc_Net],  //Sales in Country's Currency
        FILTER(VALUES('PnL Transaction Lines'[INVOICE_CURRENCY]),
            'PnL Transaction Lines'[INVOICE_CURRENCY] = CountryCurrency
        )
    )
    +
    SUMX(VALUES(Subsidiaries[CURRENCY]),
        VAR SubCurrency = SELECTEDVALUE(Subsidiaries[CURRENCY])

        RETURN
        SUMX(VALUES(Date_Calendar[MonthEnding]),
            VAR EffectiveDate = SELECTEDVALUE(Date_Calendar[MonthEnding])

            RETURN
            CALCULATE([SalesRep_DevRev_Sub_Net], //Sales in Subsidiary's Currency
                FILTER(VALUES('PnL Transaction Lines'[INVOICE_CURRENCY]),
                    NOT 'PnL Transaction Lines'[INVOICE_CURRENCY] = CountryCurrency
                )
            ) *
            CALCULATE(MIN('Exchange Rates Avg'[Average Monthly Exchange Rate]),
                FILTER('Exchange Rates Avg',
                    'Exchange Rates Avg'[EFFECTIVEDATE] = EffectiveDate &&
                    'Exchange Rates Avg'[BASE_CURRENCY] = SubCurrency &&
                    'Exchange Rates Avg'[LOCAL_CURRENCY] = CountryCurrency
                )
            )
        )
    )
)

[some_bih]

Hi @ghaines OK for part upstream. For DAX part, did you have time to check link and analyse your scenario, if possible fit into it?

For part I am trying to calculate all sales converted to the country's main currency,  only your measures is not enought to provide some possible solutions.

Please, if possible, share your model data with sample input and expected output as your request is grain of "transaction" my understanding. 

Still,  if you have all inputs, then you only need workround for rate transformation.

File sharing could be via Office 365 link or some other like dropbox...

[ghaines · Accepted solution]

Thanks for your help.  The issue though was in the implementation of SUMX, as suspected.

I was using the pattern of 
SUMXV(VALUES(Table1[Column1])
    SELECTEDVALUE(Table1[Column1]) * 1.3
)

This will not typically work, although I suspect it would work if you wrapped the "SELECTEDVALUE" in a "CALCULATE".  SUMX provides row context on the rows of the table of the iterator.  Removing the "SELECTEDVALUE" fixed my issue.  Here is my final code (I did change the name of one of the columns in one of my fact tables (INVOICE_CURRENCY -> TRANSACTION_CURRENCY), but the model was otherwise unchanged):

SalesRep_DevRev_TransformLoc_Net = 
SUMX(VALUES(Country[Local Currency]),
    VAR CountryCurrency = Country[Local Currency]
    

    RETURN
    CALCULATE([SalesRep_DevRev_Loc_Net],
        
        FILTER(VALUES('PnL Transaction Lines'[TRANSACTION_CURRENCY]),
            'PnL Transaction Lines'[TRANSACTION_CURRENCY] = CountryCurrency
        )
    )
    +
    SUMX(VALUES(Subsidiaries[CURRENCY]),
        VAR SubCurrency = Subsidiaries[CURRENCY]

        RETURN
        SUMX(VALUES(Date_Calendar[MonthEnding]),
            VAR EffectiveDate = Date_Calendar[MonthEnding]

            RETURN
            CALCULATE([SalesRep_DevRev_Sub_Net],
                FILTER(VALUES('PnL Transaction Lines'[TRANSACTION_CURRENCY]),
                    NOT 'PnL Transaction Lines'[TRANSACTION_CURRENCY] = CountryCurrency
                )
            ) *
            CALCULATE(MIN('Exchange Rates Avg'[Average Monthly Exchange Rate]),
                FILTER('Exchange Rates Avg',
                    'Exchange Rates Avg'[EFFECTIVEDATE] = EffectiveDate &&
                    'Exchange Rates Avg'[BASE_CURRENCY] = SubCurrency &&
                    'Exchange Rates Avg'[LOCAL_CURRENCY] = CountryCurrency
                )
            )
        )
    )
)

[some_bih]

Hi @ghaines if your solution work mark it as solution so other member  of community could eventually see it and reuse it.

[some_bih]

Hi @ghaines check link and find your scenario.

Please, try to apply the currency exchange before importing it into pbi model.

[ghaines]

This can't really be done.  Base currency for the country is discovered by mapping from a subsidiary table with a connecting table to account for exceptions as they are discovered.  Average XR is calculated within power query during load, has many steps to build out the table and account for any missing data, and is a poor candidate for merging into a fact table with millions of rows (twice).  This calculation is only required for a small subset of the transactions table, it's best handled with DAX.  Please address the general section of the question if possible.