SUMX - Currency Conversion

[Anonymous]

Hi,

We have a requirement to calculate the currency conversion and the DAX that is being used is simple - 

Sales Amount = SUMX (Sales, Sales[SalesAmt]*CALCULATE ( SUM ( ExchangeRate[ExchRate] ), USERELATIONSHIP ( Sales[Currency], ExchangeRate[Currency] ) )) 

This is working fine when using on small data sets but when it has to loop through millions of records it giving a memory error as it has to hold large volume of data in the memory. 

Sales Transactions -

CompanyCode	DocumentNo	Currency	SalesAmount
100	1001	EUR	100
100	1002	EUR	200
100	1003	CAD	100
100	1004	CAD	400

Exchange Rate - 

Currency	-->USD Rate
EUR	1.2
CAD	.77

In the above example, there are 4 transactions and looping thorugh all those records is impacting the performance.

If we can apply the processing as below, I think we can aviod SUMX altogether. 

1. Use SUM or some other function and summarize the data at the currency code level/ granularity - 2 records ( 2 currencies - CAD & EUR ).
2. Apply the rate for those 2 records.

Step 1- 

CompanyCode	Currency	SalesAmount
100	EUR	300
100	CAD	500

Step 2-

CompanyCode	Currency	SalesAmount	Rate	Amount
100	EUR	300	1.2	360
100	CAD	500	.77	385

Result -

CompanyCode	Amount
100	745

Please advise the DAX to achive this. Thank you in advance!

[v-gizhi-msft · Accepted solution]

Hi,

Pleaase try to create this calculated table:

Table = 
ADDCOLUMNS (
    SUMMARIZE (
        'Sales Transactions',
        'Sales Transactions'[CompanyCode],
        'Sales Transactions'[Currency],
        "Sales", SUM ( 'Sales Transactions'[SalesAmount] )
    ),
    "Rate", CALCULATE (
        SUM ( 'Exchange Rate'[ -->USD Rate] ),
        USERELATIONSHIP ( 'Sales Transactions'[Currency], 'Exchange Rate'[Currency] )
    )
)

Then try this measure:

Amount = SUMX(DISTINCT('Table'[Currency]),CALCULATE(MAX('Table'[Sales])*MAX('Table'[Rate])))

The result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

Bumping up...