Forum Discussion

dan-dan's avatar
dan-dan
Frequent Visitor
3 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 ra...
Need Help
  • dan-dan's avatar
    dan-dan
    3 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])
    )

     

v-yanjiang-msft's avatar
v-yanjiang-msft
Icon for Community Support rankCommunity Support
3 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
3 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])
    )