Forum Discussion
Compute effective approach; sumx, summarize, sum - case of transactional sales data and exch. rates
I continued a bit on my own.
Current trial;
Sales Revenue =
VAR ExchangeRate = Calculate(
min('Exchange Rates'[Rate]),
filter('Exchange Rates',
'Exchange Rates'[From Currency]=MIN('Sales Revenue and Cost'[Currency]) &&
'Exchange Rates'[Date]<=MAX('Sales Revenue and Cost'[Date]) &&
'Exchange Rates'[To Currency]=SELECTEDVALUE('Reporting Currency'[Currency])
)
)
RETURN
SUMX(
SUMMARIZE('Sales Revenue and Cost','Sales Revenue and Cost'[Currency]),
[Sales Revenue LCY] * ExchangeRate
)With [Sales Revenue LCY] being;
Sales Revenue LCY = SUM('Sales Revenue and Cost'[Line Amount])
It seems to perform better compute and memorize-wise, and a visual with Year-Month calculates correct Reporting Currency Amount but totals the columns incorrectly with total sum of LCY times "Last Exchange Rate"...
And I still have my doubts if this is best practice at all 😞
Hi LasseL ,
Based on your description, the current trial will have a better performance beacuse it uses variable and summazire a table in the sumx() function.
"but totals the columns incorrectly with total sum of LCY times "Last Exchange Rate""
What is your expected output? Could you please consider sharing more details like sample file, screenshots etc. about this issue for further discussion?
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Best Regards,
Yingjie Li
- LasseL5 years ago
Helper I
Dear Yingjie,
Thank you so much for getting back.
For confidential reasons I can't share the actual PBIX and data that I am working in, but I tried to reproduce a demo dataset (excel) and a PBIX fil with similar setup.
Attached;
- PBIX:
https://dbizdk.sharepoint.com/:u:/s/dbizApS/EUMQiIGSK8FPow-lVdwdYTYBlcqcfL80iQSwAmtKIyU92Q?e=VgX1KV - Excel sources:
https://dbizdk.sharepoint.com/:x:/s/dbizApS/Ec22y1l-VhVLqxrYHe9j7ogB_ZQi0cZOOhOkZzMGvrX4ag?e=5np5Z5 - Update, download link replaced by wetransfer: https://we.tl/t-264shFE8H6
What I would expect is the totals of Sales to be correct with latest exchange rate when choosing either DKK or EUR as a reporting currency;
Does this help?
- v-yingjl5 years ago
Community Support
Hi LasseL ,
Seems like I could not download the sample due to the access. Perhaps you can modify the visit access and consider re-sharing the sample file.
Best Regards,
Yingjie Li
- LasseL5 years ago
Helper I
Hi again Yingjie,
My sincere appologies, I will try another means; https://we.tl/t-264shFE8H6
Does this work?
Best regards,
Lasse
- PBIX: