Forum Discussion

gayle_t's avatar
gayle_t
Regular Visitor
9 years ago

Sumx and currency conversion doesnt return correct results

Hi

Trying to do a simple currency conversion of a measure.

 

Measure_Convert := sumx(Data_all, DIVIDE([TotalRevenue], [Exchange Rate])

 

TotalRevenue is measure that uses calculate with a few conditions. (in different currencies)

Exchange Rate is a calculated column with the following formula: (Returns 1 if there is no relevant rate, as only certain rows require conversion)

if(RELATED('Exchange Rates'[Rate])=0,1,RELATED('Exchange Rates'[Rate])))

 

The results seem to be working, however i have some results that show me a weird conversion rate.

Eg: 0.998, 0.996. 

I only expect to see 2 conversion rates, 1 or 2.5 

 

What would an alternative or the simplest way to convert a measure to a common currency? 

 

 

 

 

Need Help

3 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    9 years ago

    Seems like you are taking a wrong approach. It would be better if you can post snapshot of your datamodel and sample data.

     

     

    Thanks & Regards,

    Bhavesh

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    gayle_t

     

    As BhaveshPatel said, you’d better post snapshot of your data model and sample data. I tested with following two simple tables (“Data_all” and “Exchange_Rates” have relationship with “ID” key) and didn’t experience the problem you mentioned above.

     

     

    Best Regards,

    Herbert

    • gayle_t's avatar
      gayle_t
      Regular Visitor
      9 years ago

      Thanks v-haibl-msft and BhaveshPatel

       

      Unfortunately my data is confidential, dont think i can post here. I would need to randomise the data and see whether the issue still persists.

       

      I will post the data model and samples soon.