Combining Data from Different Databases/Organizations?

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:

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.

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.