Combining Data from Different Databases/Organizations?

[rmussetter]

Hi, my organization recently went through a merger and I've been tasked with building a combined balanced scorecard in Power BI to demonstrate the impact of the merger in a variety of areas. One such area is employee turnover rate. For one side of the organization, I have access to a table of employees with hire dates and termination dates, if applicable, that I was able to build a set of measures for to calculate a turnover rate - see screenshots for a sample of the data structure and the meausures I built utilizing Irfan Bakaly's fantastic video (https://www.youtube.com/watch?v=4WRJG8fyhPI😞

However, from the other side of the organization, I only have access to a spreadsheet with percentages already calculated, and no raw data:

I'm able to at least pull this into the semantic model for the report I'm utilizing but not sure how to proceed so that I can display the data for both organizations in a single visual:

My working theory is that I could possibly generate a table in the semantic model that utilizes the "Turnover Rate" measure I've created for the first side of the organization year by year - am I on the right track and if so, any advice on how to proceed? Thanks so much.

[Shravan133 · Accepted solution]

To display the employee turnover rate data from both sides of the organization in a single visual, you can create a unified table that combines the calculated turnover rates from your raw data with the provided percentages from the spreadsheet

Step 1: Create Turnover Rate Measure for First Organization

You mentioned you have already created the turnover rate measure for the first organization using hire and termination dates. Assuming you have a measure like this:

Turnover Rate =

DIVIDE(

    CALCULATE(

        COUNTROWS(EmployeeTable),

        NOT(ISBLANK(EmployeeTable[TerminationDate]))

    ),

    CALCULATE(

        COUNTROWS(EmployeeTable),

        NOT(ISBLANK(EmployeeTable[HireDate]))

    )

)

Step 2: Create a Table for Turnover Data from the Spreadsheet

Import the spreadsheet with the pre-calculated turnover rates for the second organization into Power BI. Create a table with the following structure:

Year	TurnoverRate2
2021	0.10
2022	0.12
2023	0.15
...	...

Step 3: Create a Unified Table

You need to create a unified table that combines the turnover rates from both organizations. You can do this using a calculated table in Power BI.

1. Create a Calendar Table (if you don't already have one):

Calendar = CALENDAR(MIN(EmployeeTable[HireDate]), MAX(EmployeeTable[TerminationDate]))

1. Create the Unified Table:

UnifiedTurnoverRates =

UNION(

    SELECTCOLUMNS(

        ADDCOLUMNS(

            SUMMARIZE(

                EmployeeTable,

                EmployeeTable[Year]

            ),

            "TurnoverRate", [Turnover Rate],

            "Organization", "Org1"

        ),

        "Year", EmployeeTable[Year],

        "TurnoverRate", [TurnoverRate],

        "Organization", "Org1"

    ),

    SELECTCOLUMNS(

        SpreadsheetTable,

        "Year", SpreadsheetTable[Year],

        "TurnoverRate", SpreadsheetTable[TurnoverRate],

        "Organization", "Org2"

    )

)

Step 4: Create Visualizations

Now that you have a unified table, you can create visualizations to display the turnover rates for both organizations in a single chart.

1. Create a Line Chart:
• Axis: Year
• Values: TurnoverRate
• Legend: Organization

Final Steps

• Ensure Data Types: Make sure that the Year and TurnoverRate columns in the unified table are of the correct data type.
• Formatting: Format the TurnoverRate column as a percentage.
• Slicer: Add a slicer for Organization if you want to filter the visual by organization.

[Anonymous · Accepted solution]

Your solution is so great Shravan133 

Hi, @rmussetter 

You mentioned that you can't get the source data for the second data, so your working logic is to create a calculated table in the data source, just like you showed:

For data source 2, you can use the following M code to convert the table you got to the table calculated by data source 1 as it is:

#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company Wide"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[#"Company Wide"]), "Company Wide", "Value", List.Sum)

You can then use the same year column in your visuals to make comparisons. The above gives me a personal opinion, and it is also a good way to achieve comparison.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shravan133 · Accepted solution]

To display the employee turnover rate data from both sides of the organization in a single visual, you can create a unified table that combines the calculated turnover rates from your raw data with the provided percentages from the spreadsheet

Step 1: Create Turnover Rate Measure for First Organization

You mentioned you have already created the turnover rate measure for the first organization using hire and termination dates. Assuming you have a measure like this:

Turnover Rate =

DIVIDE(

    CALCULATE(

        COUNTROWS(EmployeeTable),

        NOT(ISBLANK(EmployeeTable[TerminationDate]))

    ),

    CALCULATE(

        COUNTROWS(EmployeeTable),

        NOT(ISBLANK(EmployeeTable[HireDate]))

    )

)

Step 2: Create a Table for Turnover Data from the Spreadsheet

Import the spreadsheet with the pre-calculated turnover rates for the second organization into Power BI. Create a table with the following structure:

Year	TurnoverRate2
2021	0.10
2022	0.12
2023	0.15
...	...

Step 3: Create a Unified Table

You need to create a unified table that combines the turnover rates from both organizations. You can do this using a calculated table in Power BI.

1. Create a Calendar Table (if you don't already have one):

Calendar = CALENDAR(MIN(EmployeeTable[HireDate]), MAX(EmployeeTable[TerminationDate]))

1. Create the Unified Table:

UnifiedTurnoverRates =

UNION(

    SELECTCOLUMNS(

        ADDCOLUMNS(

            SUMMARIZE(

                EmployeeTable,

                EmployeeTable[Year]

            ),

            "TurnoverRate", [Turnover Rate],

            "Organization", "Org1"

        ),

        "Year", EmployeeTable[Year],

        "TurnoverRate", [TurnoverRate],

        "Organization", "Org1"

    ),

    SELECTCOLUMNS(

        SpreadsheetTable,

        "Year", SpreadsheetTable[Year],

        "TurnoverRate", SpreadsheetTable[TurnoverRate],

        "Organization", "Org2"

    )

)

Step 4: Create Visualizations

Now that you have a unified table, you can create visualizations to display the turnover rates for both organizations in a single chart.

1. Create a Line Chart:
• Axis: Year
• Values: TurnoverRate
• Legend: Organization

Final Steps

• Ensure Data Types: Make sure that the Year and TurnoverRate columns in the unified table are of the correct data type.
• Formatting: Format the TurnoverRate column as a percentage.
• Slicer: Add a slicer for Organization if you want to filter the visual by organization.