How to create and use User Defined Function for Currency Conversion in Power BI

How to apply the Currency Conversion in Power BI using User Defined Function
Scenario:

Imagine you are a global retail company analyzing sales across multiple countries. Your sales data is stored in a fact table (fct_Orders_Sample) with gross sales values in the base currency (USD). Business users want to view Gross Revenue in their local currency, either based on the country they select in a report or by explicitly passing a currency code (like GBP or EUR).

In Power BI, a User Defined Function (UDF) is a modular block of DAX logic that you can parameterize and reuse across multiple measures. Instead of repeating the same calculation in different places, you encapsulate it into a function and pass arguments to control its behavior.

We can achieve the above Scenario as explained in below Methods.

Data Model:

Relationships:

Notes:

ref_Currency_Mapping: This is a Currency Rate mapping table which is disconnected from the Data Model and will be used as Lookup table for the sample of Currency Conversion Rates.

ref_Currency_Mapping =

DATATABLE(

    "Currency_ID", INTEGER,

    "Country_ID", INTEGER,

    "Country_Name", STRING,

    "Currency_Code", STRING,

    "Conversion_Rate", DOUBLE,

    {

        {101, 234, "India",         "INR", 92.40},   -- 1 USD ≈ 92.4 INR

        {102, 235, "Japan",         "JPY", 157.00},  -- 1 USD ≈ 157 JPY

        {103, 236, "United States", "USD", 1.00},    -- Base Currency

        {104, 237, "Canada",        "CAD", 1.37},    -- 1 USD ≈ 1.37 CAD

        {105, 238, "United Kingdom","GBP", 0.75},    -- 1 USD ≈ 0.75 GBP

        {106, 239, "Spain",         "EUR", 0.85}     -- 1 USD ≈ 0.85 EUR

    }

)

Base Measure:

Gross Revenue = Sum(fct_Orders_Sample[Gross_Sales])

Note: The Default Currency for [Gross Revenue] is USD when there is no specific Currency selected.

Now we can do the Currency Conversion based on the above mapping table, using the below methods.

Method 1: Conversion Based on Selected Country:
In this Method, based on the selected Country from Slicer of a dim_Country[Country], we will apply the Conversion rate.

Selected Currency Conversion Rate =

VAR _SelCurCode= SELECTEDVALUE(dim_Country[Currency_Code], "USD")

VAR _Conversion_Rate =

    LOOKUPVALUE(

        ref_Currency_Mapping[Conversion_Rate],

        ref_Currency_Mapping[Currency_Code], _SelCurCode

    )

RETURN

IF(ISBLANK(_Conversion_Rate), 1, _Conversion_Rate)

Gross Revenue (Selected Currency) =

[Gross Revenue] * [Selected Currency Conversion Rate]

We have used the below function for Dynamic Format for above measure.

fx_CurrencyFormat(BLANK())

The definition of the Function for Dynamic Currency Format:

fx_CurrencyFormat = ( CurrencyCode: string val) =>

VAR _Selected_Currency = SELECTEDVALUE(dim_Country[Currency_Code], "USD")

VAR CurrencyCode = COALESCE(CurrencyCode, _Selected_Currency)

RETURN

    SWITCH(

        CurrencyCode,

        "INR", "₹ #,0.00",       -- Indian Rupee

        "JPY", "¥ #,0",          -- Japanese Yen

        "USD", "$ #,0.00",       -- US Dollar

        "CAD", "$ #,0.00",       -- Canadian Dollar

        "GBP", "£ #,0.00",       -- Pound Sterling (UK/England)

        "EUR", "€ #,0.00",       -- Euro (Spain, Eurozone)

        "$ #,0.00"               -- Default USD

    )

Result:

Method 2: Conversion Using a User Defined Function:
In this method, we will use a User Defined Function with a Currency Code Parameter, enables us to explicitly pass a specific Currency Code (e.g., always show GBP regardless of selected country).

In Power BI, we can create a User Defined Function from Model view as shown below.

The below Function is a Model dependent, which depends on the ref_Currency_Mapping table from the Data Model.

fx_CurrencyConversion = ( CurrencyCode: string val) =>

VAR _Selected_Currency = SELECTEDVALUE(dim_Country[Currency_Code], "USD")

VAR _Currency_Passed = COALESCE(CurrencyCode, _Selected_Currency)

VAR _Conversion_Rate =

    LOOKUPVALUE(

        ref_Currency_Mapping[Conversion_Rate],

        ref_Currency_Mapping[Currency_Code], _Currency_Passed

    )

RETURN

IF(ISBLANK(_Conversion_Rate), 1, _Conversion_Rate)

Gross Revenue (Selected Currency in fx) = [Gross Revenue] * fx_CurrencyConversion("GBP")

The below Function is used for the Dynamic format string:

fx_CurrencyFormat("GBP")

Result:

Note: 

You can customize this User Defined Function to suit your own scenario. Whether you are working with different currencies, adjusting for tax rates, or applying other business rules.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

How to create and use Calculation Groups in Power BI

How to define and use Calculation Groups in Power BI
A Calculation Group is a feature in Power BI that lets you define reusable logic once and apply it across multiple measures. 

Instead of writing separate DAX formulas for each measure (like [Sales] and [Orders]), we can create calculation items (such as Current Month, Previous Month, Month‑over‑Month Growth) that automatically work with any base measure.

Scenario:
Suppose we have 2 measures as [Sales] and [Orders] as base measures in our Data Model. For which we want to calculate the values for Previous Month and Monthly Growth by using the Calculation Groups, defined in Power BI Model as discussed below.

Data Model:

Relationships:

Base Measures:

Sales = Sum(fct_Orders_Sample[Gross_Sales])

Orders = COUNT(fct_Orders_Sample[Order_ID])

Now, we can define Calculation group with below Calculation Items to calculate the values for Previous Month and Monthly Growth.

The Calculation Groups can be defined from Model view of the Power BI:

MoM_Growth_Group: It is the Calculation Group Name

MoM_Growth_Field: It is the Column name of Calculation Group. This Column will be used in the Visuals to display the Calculation Items (Measures).

In the Table view, it will display as per below:

Here, Index is the Ordinal value. The default ordinal value for a calculation item/measure is '-1', and its Index starts from 0.

Current Month Value =

VAR _Enable_Flag = 1

VAR _Result =SELECTEDMEASURE()

RETURN

IF(_Enable_Flag, _Result)

Prev Month Value =

VAR _Enable_Flag = 1

VAR _Result =

CALCULATE(SELECTEDMEASURE(),

        DATEADD( Dim_Calendar[DimDate], -1, MONTH)

        )

RETURN

IF(_Enable_Flag, _Result)

% MoM Growth =

VAR _Enable_Flag = 1

VAR _CM =

    SELECTEDMEASURE()

VAR _PM =

    CALCULATE(

        SELECTEDMEASURE(),

        DATEADD(Dim_Calendar[DimDate], -1, MONTH)

    )

VAR _Growth = DIVIDE((_CM - _PM), _PM)

VAR _Msr_Format = FORMAT(_Growth, "00.0%;00.0%")

VAR _Result =

    SWITCH(

        TRUE(),

        ISBLANK(_Growth), _Msr_Format & " " & UNICHAR(9899), --Grey Circle

        ISBLANK(_CM), _Msr_Format & " " & UNICHAR(9899),     --Grey Circle

        _Growth = 1, _Msr_Format & " " & UNICHAR(9899),      --Grey Circle

        _Growth < 0, _Msr_Format & " " & UNICHAR(128308),    --Red Circle

        _Growth > 0, _Msr_Format & " " & UNICHAR(128994)     --Green Circle

    )

RETURN

IF(AND(_Enable_Flag, NOT ISBLANK(_Growth)), _Result)

Notes:

We can set _Enable_Flag = 0 to a disable or not to display a Calculation Item from Group.

The Dynamic format string "#.##" used for the Calculation Items [Current Month Value] and the [Previous Month Value].

The Dynamic format string "00.0%;00.0%" used for the Calculation Item [% MoM Growth]

The output of the Calculation Group and its Items is as per below. 

New Card Visual for Orders:

For the new Card Visual, we need to use the MoM_Growth_Field in the Categories section.

New Card Visual for Sales:

Matrix Visual for both Orders and Sales Measures:

For the Matrix Visual, we need to use the MoM_Growth_Field in the Columns section.

Notes:
1) Currently, the Calculation groups are only fully supported in visuals that can pivot measures dynamically, like the Matrix (and Pivot‑style visuals). For Table visual they will not work.

2) Calculation Groups in Power BI can override or disable default aggregations because they apply the transformations to measures at query time. 

This means the original aggregation logic (like SUM) may be replaced by the calculation group’s definition. To avoid unexpected results, always create and use explicit measures for the required aggregations.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

How to use Dynamic SQL to Consolidate Data from multiple Tables with different Structures

How to use Cursor in SQL to Consolidate Data from various Tables with different Structures

Scenario:

Suppose you are working in a pharmaceutical company where information is distributed across several systems. Each system captures different attributes:

• Clinical trials record patient groups, their clinical responses, and efficiency rates.
• Production records track manufacturing details such as batch yield ratios and batch quantities.
• Sales transactions capture commercial data like regions, customer types, and sales amounts.

While this design works well for transactional systems, it becomes difficult when you need to analyze or report across all of them in a consistent way.
For example, management may want to see how a drug performed in trials, how efficiently it was produced, and how much revenue it generated, in a consolidated view.

The challenge is to create a Unified_Data_Repository that standardizes data from these diverse sources. Instead of wide tables with many columns, the goal is to reshape the data into a normalized tall format. 

In this structure, each column value is transformed into a row, making it easier to query, compare, and report across the various heterogeneous tables.

The consolidated Output table follows a simple schema as per below:

• ID: represents the original row identifier from the source table.
• Criteria_Name: the name of the column being transformed.
• Criteria_Value: the actual value from that column, stored as text.
• Src_Object_Name: the source table name, ensuring traceability.
• _LastModified_On: a timestamp marking when the record was inserted.

We can achieve this Scenario using Dynamic SQL with Cursor as explained below:

-----------------------------------------------------------------

Step1: Create Sample Input Source Tables:

-----------------------------------------------------------------

-- Sample of Clinical trial data

CREATE TABLE [dbo].[Clinical_Trials] 

(

    Trial_ID INT,

    Drug_Code VARCHAR(50),

    Patient_Group VARCHAR(50),

    Clinical_Response VARCHAR(100),

    Efficiency_Rate DECIMAL(10,2)

);

INSERT INTO [dbo].[Clinical_Trials] VALUES

(1001, 'Drug_A01', 'Adults', 'Positive', 87.5),

(1002, 'Drug_B02', 'Children', 'Neutral', 65.0),

(1003, 'Drug_C03', 'Elderly', 'Negative', 42.3);

SELECT * FROM [dbo].[Clinical_Trials]

-- Sample of Production records

CREATE TABLE [dbo].[Production_Records] 

(

    Batch_ID INT,

    Drug_Code VARCHAR(50),

    Plant_ID VARCHAR(50),

    Batch_Yield_Ratio DECIMAL(10,2),

    Batch_Quantity DECIMAL(10,2)

);

INSERT INTO [dbo].[Production_Records] VALUES

(2001, 'Drug_A01', 'Plant_001', 95.2, 1200.5),

(2002, 'Drug_B02', 'Plant_002', 89.7, 980.0),

(2003, 'Drug_C03', 'Plant_003', 76.4, 750.2);

SELECT * FROM [dbo].[Production_Records]

-- Sample of Sales transactions

CREATE TABLE [dbo].[Sales_Transactions] 

(

    Sale_ID INT,

    Region VARCHAR(50),

    Drug_Code VARCHAR(50),

    Customer_Type VARCHAR(50),

    Sales_Amount DECIMAL(10,2)

);

INSERT INTO [dbo].[Sales_Transactions] VALUES

(3001, 'North America', 'Drug_A01', 'Hospital', 15000.0),

(3002, 'Europe', 'Drug_B02', 'Pharmacy', 12000.5),

(3003, 'Asia', 'Drug_C03', 'Distributor', 9800.0);

SELECT * FROM [dbo].[Sales_Transactions]

-----------------------------------------------------------------

Step 2: Create the Unified Output Table to store final Output Data:

-----------------------------------------------------------------

CREATE TABLE [dbo].[Unified_Data_Repository] 

(

    ID INT,

    Criteria_Name VARCHAR(200),

    Criteria_Value VARCHAR(200),

    Src_Object_Name VARCHAR(200),

    _LastModified_On DATETIME

);

-----------------------------------------------------------------

Step 3: Create a Temp Table with Source Table Names and Key Columns:

-----------------------------------------------------------------

IF OBJECT_ID('tempdb..##Src_Table_Mapping') IS NOT NULL 

DROP TABLE ##Src_Table_Mapping;

CREATE TABLE ##Src_Table_Mapping (

    Source_Table VARCHAR(128),

    Key_Column  VARCHAR(128)

);

INSERT INTO ##Src_Table_Mapping VALUES

('Clinical_Trials', 'Trial_ID'),

('Production_Records', 'Batch_ID'),

('Sales_Transactions', 'Sale_ID');

SELECT * FROM ##Src_Table_Mapping;

-----------------------------------------------------------------

Step 4: Dynamic SQL Query with Cursor to consolidate Data from all Source tables

-----------------------------------------------------------------

-- Declare variables to hold table name and SQL statement

DECLARE @TableName NVARCHAR(200);

DECLARE @sql NVARCHAR(MAX);

DECLARE @KeyColumn NVARCHAR(200);

-- Define a Cursor that will loop through all Source table names

DECLARE _CursorX Cursor FOR

SELECT Source_Table, Key_Column FROM ##Src_Table_Mapping;

-- Open the Cursor (initializes the result set)

OPEN _CursorX;

-- Fetch the first record (first table name from mapping)

FETCH NEXT FROM _CursorX INTO @TableName, @KeyColumn;

-- Loop until all records are processed

WHILE @@FETCH_STATUS = 0

BEGIN

    SET @sql = '';

    -- Build dynamic SQL for the _CursorXrent table

    -- This Pivots columns into rows and inserts them into Unified_Data_Repository

    SELECT @sql = 

        'INSERT INTO [dbo].[Unified_Data_Repository] 

        (ID, Criteria_Name, Criteria_Value, Src_Object_Name, _LastModified_On) ' +

        (

        SELECT STRING_AGG(

            'SELECT ' + @KeyColumn + ' AS ID, ''' + COLUMN_NAME + ''' AS Criteria_Name, ' +

            'CAST(' + COLUMN_NAME + ' AS NVARCHAR(200)) AS Criteria_Value, ' +

            '''' + @TableName + ''' AS Src_Object_Name, GETDATE() AS _LastModified_On ' +

            'FROM [dbo].[' + @TableName + ']',

            ' UNION ALL '

            )

        FROM INFORMATION_SCHEMA.COLUMNS

        WHERE TABLE_NAME = @TableName

        AND COLUMN_NAME <> @KeyColumn  -- Exclude Key column from Pivot

        );

    -- Execute the dynamic SQL for this table

    EXEC(@sql);

    -- Fetch the next record (next table name)

    FETCH NEXT FROM _CursorX INTO @TableName, @KeyColumn;

END

-- Close the Cursor (release resources)

CLOSE _CursorX;

-- Deallocate the Cursor (remove definition)

DEALLOCATE _CursorX;

-----------------------------------------------------------------

 Step 5: View the final consolidated Output

-----------------------------------------------------------------

SELECT * 

FROM [dbo].[Unified_Data_Repository]

ORDER BY Src_Object_Name, ID;

-----------------------------------------------------------------

Data Analysis and Insights:

-----------------------------------------------------------------

We can use the consolidated unified dataset to analyze and study the relationship between clinical outcomes and commercial performance. 

By pivoting attributes such as Drug_Code, Efficiency_Rate, and Sales_Amount, the dataset can be transformed into an analytical view that highlights how scientific effectiveness connects with market adoption.

The example below demonstrates how a focused query can turn the normalized repository into actionable insights:

WITH Drug_Data AS (

    SELECT Src_Object_Name, Criteria_Name, Criteria_Value, ID

    FROM Unified_Data_Repository

    WHERE Criteria_Name IN ('Drug_Code','Efficiency_Rate','Sales_Amount')

)

SELECT t1.Criteria_Value AS Drug_Code,

       MAX(CASE WHEN t2.Criteria_Name = 'Efficiency_Rate' THEN t2.Criteria_Value END) AS Efficiency_Rate,

       MAX(CASE WHEN t2.Criteria_Name = 'Sales_Amount' THEN t2.Criteria_Value END) AS Sales_Amount

FROM Drug_Data t1

INNER JOIN Drug_Data t2 ON t1.ID = t2.ID

WHERE t1.Criteria_Name = 'Drug_Code'

GROUP BY t1.Criteria_Value;

Insights Derived from Analysis:

By using the consolidated dataset and pivoting attributes like Drug_Code, Efficiency_Rate, and Sales_Amount, we can understand how clinical effectiveness relates to the performance of the sales. From this analysis, we can derive the following key insights.

1) Clinical vs. Commercial Performance:

• Drug_A01 shows the highest efficiency rate (87.5%) and the strongest sales (15,000). This indicates strong clinical success translating into strong market adoption.
• Drug_C03 has the lowest efficiency (42.3%) and the weakest sales (9,800), suggesting limited clinical effectiveness is reflected in lower demand.

2) Correlation Between Metrics:

• Drug_A01 shows a positive correlation: higher efficiency rates tend to align with higher sales amounts.
• Drug_B02, with moderate efficiency (65%) but relatively strong sales (12,000.5), shows that commercial performance can sometimes outpace clinical effectiveness, possibly due to market factors like pricing, distribution, or brand strength.

3) Strategic Decision Support:

• Drug_B02 may need further clinical improvement to match its commercial success, ensuring long‑term sustainability and patient satisfaction.
• Drug_C03 could require both clinical enhancements and stronger commercial strategies to improve its overall performance.

Note:

This article shows how Dynamic SQL with Cursors can be used to consolidate data. The examples are for learning purposes, and you can refine and adapt the logic to fit your own requirements.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------