Forum Discussion

dan-dan's avatar
dan-dan
Frequent Visitor
4 years ago
Solved

Incorrect or Missing Currency Totals

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.

 

Need Help
  • dan-dan's avatar
    dan-dan
    4 years ago

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