Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi
Need some help here ... Tryitng to compute a "Currency Impact". Given a "base" table with, say, sales in local currency, and an exchange rate; given a "fxrate" table that is sliced to filter to a unique reference, how can I compute line-by-line and get the total ? The formula needs to be efficient, because there are easily 500'000 rows in "base". Not easy to explain the issue, so I made a little drawing! Any help is higly appreciated ...
Solved! Go to Solution.
HI @Lecram36
First, you should know that:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result in a visual.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Second, for your case, you could try this way as below:
Step1:
Create a relationalship between Datatable and FXTable by Currency column.(This relationship will be many to many)
Step2:
Then create a measure as below:
Impact =
SUMX('Data Table', CALCULATE(SUM('Data Table'[Sales])-SUM('Data Table'[Sales])*DIVIDE(SUM('Data Table'[FxRate]),SUM(FXTable[RefFxRate]))))
Result:
and here is sample pbix file, please try it.
Regards,
Lin
HI @Lecram36
First, you should know that:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result in a visual.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Second, for your case, you could try this way as below:
Step1:
Create a relationalship between Datatable and FXTable by Currency column.(This relationship will be many to many)
Step2:
Then create a measure as below:
Impact =
SUMX('Data Table', CALCULATE(SUM('Data Table'[Sales])-SUM('Data Table'[Sales])*DIVIDE(SUM('Data Table'[FxRate]),SUM(FXTable[RefFxRate]))))
Result:
and here is sample pbix file, please try it.
Regards,
Lin
@Lecram36 Based on your data table, it should be something like this, add as a column
Sales Impact =
Table[Sales] - ( DIVIDE ( ( Table[Sales] * Table[FxRate] ), Table[RefFxRate] ) )
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k Nope, unfortunately not that simple: Table[RefFxRate] is not available. It's in the excel sheet for the demo of the calculation, but it's an external value computed 'on the fly'
ok then you have to share your data the way it look and the model/relationship diagram.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
87 | |
80 | |
69 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |