Forum Discussion
Solved
Optimizing sumx
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?!!
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.
1 Reply
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.