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_Currency_Rates is as follows:
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.
Step 2:
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"
Step 3 (final):
Finally, create a Measure as "Sales_Conversion" that converts the Sales from the Source Currency to Target Currency.
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.