Optimizing sumx

[Amrselim1989]

hello,

kinldy i have very simple sumx calculation 

it iterates agients 3 million row in a table and about 300 rows in another table;

the code is 

Sum of Net Sales Value in USD = sumx('Table', 'Table'[Net Sales Value] / RELATED(RateDate[USD Price]))

the table i have is like this but with 3 millions rows and has dublicate in Date column

Date		Net Sales Value
1/1/2019		10
1/1/2019		20
1/1/2019		45
1/2/2019		67
1/2/2019		87
1/3/2019		45
1/4/2019		44

the RateDate USD is my currency to USD rate:

1/1/2019		4
1/2/2019		5
1/3/2019		5
1/3/2019		5

my code in visual gives me visual has excedded avaiabale resources, i know it's be cause the itration, can i optimize this code at least to summerize sales of a single date they divide it on USD rate, but it must be itrated on date by date to perserving the USD rate day by day?!!

[mickey64 · Accepted solution]

For your reference.

I use ’SUM’ instead of 'SUMX'.

Step 1: I make a calendar table and add 2 relationships.

Step 2: I make a measure below.

    Measure = DIVIDE(SUM('Table'[Net Sales Value]),AVERAGE('RateDate'[USD Price]))

Step 3: I make a matrix below.