Sumx and currency conversion doesnt return correct results

[gayle_t]

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? 

[v-haibl-msft]

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

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.

[BhaveshPatel]

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