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

     

4 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      dan-dan
      Frequent Visitor
      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])
    )

       

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

      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's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

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