Incorrect or Missing Currency Totals

[dan-dan]

I have set up my data model similar to what's outline here: 
https://blog.enterprisedna.co/creating-a-currency-conversion-table-in-power-bi/

The key difference is that I have annual conversion rates that only go to USD (CAD --> USD, GBP --> USD, etc). The values I need to convert are in their local currency and I need to convert it to USD. At the countryCode or currencyCode level, the calculation is correct, but the totals are either incorrect or don't show at all.

I have searched all over this forum and elsewhere to find the answer but I can't seem to find it. I have tried to modify the solutions I've found but nothing is working.

In the attached screenshot, the total I want to show is $9,476.12. If the total shows up, it most often shows $77,702.72. Also, the "rate1" metric in the table is there as a helper column. It is the same formula shown, but I'm returning the "_rate1" variable so I can make sure its returning the right value.

I know I need to use something like SUMX and/or SUMMARIZE. And I also feel like what is contributing to (or potentially the root cause of) the issue is that my "_local" variable doesn't have a value in the "Total" filter context.

Disregard the "_rate2" variable for this discussion.

[v-yanjiang-msft]

Hi @dan-dan ,

It's because for measures, it's calculated according to context. It means, although for the total row, it's not a simply sum like calculated columns, it will also perform the operation _sales * _rate1. But as you can see in the snapshot, rate1 is blank in the total row, so it's also blank in the total row of $Ad Spend USD.

Here's my solution, modify the formula like this:

$ Ad Spend USD =
VAR _year = [_Current Year]
VAR _local =
    SELECTEDVALUE ( 'Marketing'[currencyCode] )
VAR _selected = [_Currency Selected]
VAR _sales = [$ Ad Spend (local)]
VAR _rate1 =
    LOOKUPVALUE (
        annualConversionRates[exchangeRateMultiplier],
        annualConversionRates[fromCurrency], _local,
        annualConversionRates[year], _year
    )
VAR _T =
    ADDCOLUMNS ( 'Marketing', "USD", _sales * _rate1 )
RETURN
    IF (
        HASONEVALUE ( 'Marketing'[currencyCode] ),
        _sales * _rate1,
        SUMX ( _T, [USD] )
    )

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dan-dan · Accepted solution]

You got me on the right path. I was able to figure it out. Here is the final DAX:

$ Ad Spend USD = 
VAR _year = [_Current Year]
VAR _local = MAX( 'Marketing'[currencyCode] )
VAR _selected = [_Currency Selected]
VAR _sales = [$ Ad Spend (local)]
VAR _rate1 =
    LOOKUPVALUE(
        annualConversionRates[exchangeRateMultiplier],
        annualConversionRates[fromCurrency], _local,
        annualConversionRates[year], _year
    )
VAR _USD = 
ADDCOLUMNS(
    VALUES('Marketing'[currencyCode])
    ,"USD", ([$ Ad Spend (Local)] * (LOOKUPVALUE(annualConversionRates[exchangeRateMultiplier],annualConversionRates[fromCurrency], 'Marketing'[currencyCode],annualConversionRates[year], _year)))
)


RETURN
    IF(
        HASONEVALUE( 'Marketing'[currencyCode] ),
        _sales * _rate1,
        SUMX(_USD,[USD])
    )

[dan-dan]

Thank you @v-yanjiang-msft. Unfortunately, that solution did not work. The filter context is what I understand to be the issue. I just can't seem to find the workaround.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Keep only the single tab where you are facing this problem.