Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Incorrect totals with currencies

Hi everyone, 

 

Can someone help me out? 

 

The gifts in my organization are in different currencies. So I create a opportunity in PowerBI to see the sales in different currencies.

 

The data architecture looks like this: 

 

In the gift table, I created a colum with the values in USD dolars

 

 

Then I created measure: 

4.Total_z.format = if([1.Currenct_selected] = "USD", sum(X_MSF_MSZ_MSN_KPI_Gifts[USD]),

calculate(sum(X_MSF_MSZ_MSN_KPI_Gifts[USD]) *
LOOKUPVALUE(ExchangeRate[ExchangeRate],
ExchangeRate[Date], [2.Current_date],
'ExchangeRate'[Currency], [1.Currenct_selected]
)))
 
 USED: 
1.Currenct_selected = SELECTEDVALUE(ExchangeRate[Currency],"USD")
2.Current_date = MAX('Date'[Date])
 
This measure use the current selected currencie and the exchangerate on a specific date to calculate the gift information
 
 If I create a table and look to the gifts on a specific date the results are right. But sadly if PowerBI sum the results of a month (year ect.) the results are wrong. See picture below. 
 

 


For example in januari 2020. The only gift is on the 24e, which is 50 euro's. The sum of januari is 50,12 euro. 

 

It looks like PowerBI used the wrong exange rate by summerizing. 

 

Can someone tell me how I can fix this problem? 

 

Thanks in advance! 

Need Help
Tips and Tricks
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Your measure is using the exchange rate for the maximal date rather than the exchange rate for each day separately. To resolve this, you need to iterate and look up the rate for each day.

     

    Such a measure might look something like this:

    Total_z.format =
IF (
    [1.Currenct_selected] = "USD",
    SUM ( X_MSF_MSZ_MSN_KPI_Gifts[USD] ),
    SUMX (
        VALUES ( 'Calendar'[Date] ),
        CALCULATE (
            SUM ( X_MSF_MSZ_MSN_KPI_Gifts[USD] )
                * LOOKUPVALUE (
                    ExchangeRate[ExchangeRate],
                    ExchangeRate[Date], [2.Current_date],
                    'ExchangeRate'[Currency], [1.Currenct_selected]
                )
        )
    )
)

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    Your measure is using the exchange rate for the maximal date rather than the exchange rate for each day separately. To resolve this, you need to iterate and look up the rate for each day.

     

    Such a measure might look something like this:

    Total_z.format =
IF (
    [1.Currenct_selected] = "USD",
    SUM ( X_MSF_MSZ_MSN_KPI_Gifts[USD] ),
    SUMX (
        VALUES ( 'Calendar'[Date] ),
        CALCULATE (
            SUM ( X_MSF_MSZ_MSN_KPI_Gifts[USD] )
                * LOOKUPVALUE (
                    ExchangeRate[ExchangeRate],
                    ExchangeRate[Date], [2.Current_date],
                    'ExchangeRate'[Currency], [1.Currenct_selected]
                )
        )
    )
)
  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

    Try to create another measure:

     

    Result =
VAR _table =
    ADDCOLUMNS( 'table', "_measure", [4.Total_z.format] )
VAR _result =
    IF(
        HASONEVALUE( TheFieldOfRow ),
        [4.Total_z.format],
        MAXX( _table, [_measure] )
    )
RETURN
    _result

     

    Replace the value measure of the matrix with the newly created measure

     

    If this doesn't work for you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng

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