Saturday, April 16, 2022

How to convert Sales from Source Currency to Target Currency using DAX Functions in Power BI

Power BI DAX Functions to convert Sales from Source Currency to Target Currency
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:


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))

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"

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
--------------------------------------------------------------------------------------------------------

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.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog