Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
User | Count |
---|---|
113 | |
94 | |
87 | |
75 | |
65 |
User | Count |
---|---|
138 | |
112 | |
110 | |
98 | |
94 |