Scenario:
Lets suppose we have Sales in different Source Currencies like AUD, CAD, GBP, JPY, INR etc., which we would like to convert into the Target Currencies like “USD” or “EUR” based on the user selection.
If user does not selected any Target Currency then it should automatically converted to “USD” as a default Target Currency.
Lets implement the above Scenario based on the following Sample Data Model
The relationships in the Model are as per below:
The sample data of tbl_SalesOrders is as follows:
The sample data of tbl_Currency_Rates is as follows:
The sample data of tbl_Source_Currency is as follows:
The sample data of tbl_Target_Currency is as follows:
The sample data of tbl_Country is as follows:
Step 1:
Now lets create a Measure on Target Currency, and test that should return “USD” or “EUR” based on user selection. If not selection made, by default it Should return as “USD”.
This logic we will further use in our Currency Conversion measure, in later steps.
TargetCur_Select =
Var Target_Cur = IF ( ISCROSSFILTERED(tbl_Target_Currency[Target_Cur]),
IF( HASONEVALUE(tbl_Target_Currency[Target_Cur]),
SELECTEDVALUE(tbl_Target_Currency[Target_Cur])),
“USD” )
RETURN
Target_Cur
Result:
Create a measure for Conversion Rate, which should convert the Source Currency into the Target Currency like “USD” or “EUR” based on the user selection.
If user does not selected any Target Currency then it should automatically converted to “USD” as a default Target Currency.
Conver_Rate =
Var Target_Cur = IF ( ISCROSSFILTERED(tbl_Target_Currency[Target_Cur]),
IF( HASONEVALUE(tbl_Target_Currency[Target_Cur]),
VALUES(tbl_Target_Currency[Target_Cur])),
“USD” )
RETURN
CALCULATE(SUM(‘tbl_Currency Rates'[Conversion_Rate]),
FILTER(tbl_Target_Currency, tbl_Target_Currency[Target_Cur]=Target_Cur))
The above logic can be return in the following way as well, based on the Data Model.
Conver_Rate = Var
Target_Cur = IF ( ISCROSSFILTERED(tbl_Target_Currency[Target_Cur]),
IF( HASONEVALUE(tbl_Target_Currency[Target_Cur]),
SELECTEDVALUE(tbl_Target_Currency[Target_Cur])),
“USD” )
RETURN
CALCULATE(SUM(‘tbl_Currency Rates'[Conversion_Rate]),
FILTER(‘tbl_Currency Rates’,‘tbl_Currency Rates'[Target_Curncy]=Target_Cur))
If user selects the Target Currency as “EUR”
If user does not select any Target Currency then by default it will be converted to “USD”
Step 3 (final):
Finally, create a Measure as “Sales_Conversion” that converts the Sales from the Source Currency to Target Currency.
Sales_Conversion= CALCULATE([TotalSales]*‘tbl_Currency Rates’[Conver_Rate])
here, TotalSales = SUM(tbl_SalesOrders[Sales(in Millions)])
Result:
If user selects the Target Currency as “EUR”
If user does not select any Target Currency then by default it will be converted to “USD”
——————————————————————————————————–
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
——————————————————————————————————–
Be the first to comment