The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi I have a problem to Unify the Report currency based on the selected currency by the user because each line in the transaction table will include different currency
I have applied a sample of my case in the below image, I have two tables,
1-The Exchange rate table, that includes cross-mapping between all currencies
2-The Revenue Transactions table, that includes the revenue amount for each company knowing that each company has different currency
Solved! Go to Solution.
Hi @Shoukry
Hope it helps
Assuming the User Slicer is from Foregin Currency in Table1, we can pull the relevant exchange rate in Table 2 for the same month and same year using this
Applicable Exchange Rate = CALCULATE ( VALUES ( Table1[Ex Rate] ), FILTER ( ALLEXCEPT ( Table1, Table1[Foreign Currency] ), Table1[Base Currency] = SELECTEDVALUE ( Table2[Base Currency] ) && Table1[Month] = SELECTEDVALUE ( Table2[Month] ) && Table1[YEAR] = SELECTEDVALUE ( Table2[YEAR] ) ) )
Then Revenue in the selected Currency is simply
Revenue in Selected Currency = [Applicable Exchange Rate] * SELECTEDVALUE ( Table2[Revenue] )
Hi,
So what is your expected result? Which currency do you want all revenue figures to get converted to? Are you expecting another column in Table2? Please clarify.
Hi
thanks for your reply,
the end user will need to show the report in {CHF or USD or OMR} through the slicer, in this case, I want to convert all amounts that in the transaction table based on the user selection,
which mean if the user selects to show the report in CHF, I will need to convert the base currency for each company to CHF, knowing that each company in the transaction table has deferent currency
Hi @Shoukry
Hope it helps
Assuming the User Slicer is from Foregin Currency in Table1, we can pull the relevant exchange rate in Table 2 for the same month and same year using this
Applicable Exchange Rate = CALCULATE ( VALUES ( Table1[Ex Rate] ), FILTER ( ALLEXCEPT ( Table1, Table1[Foreign Currency] ), Table1[Base Currency] = SELECTEDVALUE ( Table2[Base Currency] ) && Table1[Month] = SELECTEDVALUE ( Table2[Month] ) && Table1[YEAR] = SELECTEDVALUE ( Table2[YEAR] ) ) )
Then Revenue in the selected Currency is simply
Revenue in Selected Currency = [Applicable Exchange Rate] * SELECTEDVALUE ( Table2[Revenue] )
Hi Zubair
Many thanks for your wonderful solution, the case has been solved
Thanks a lot.
Hi,
I tried this solution and was great, however it doesnt work when i want to aggregate values for a category, for example new customers and old customers, it only shows values when on indvidual customer / transaction level.
Do you have any tips for solving this?
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 |
---|---|
102 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
264 | |
120 | |
115 | |
83 | |
70 |