Adding a total matrix from a different data set

[Anonymous]

I have two HR data sets below; one is data of all employees by department and categories (total population) and the second is the data set of employees that left and their leave reason (departure data).

There is less data in the departure data set and the employee IDs are not always matching it wouldn't make sense to join.

My desired result is the last table below. The blue part is a summary of the departure data. I can do the blue part of the table in PowerBI but I cannot add the yellow and red part (summary of total employees and leave rate by department and categories). When I try it just put 16 in all rows. Is there a way to do this in PowerBI? I looked into this: https://exceleratorbi.com.au/building-a-matrix-with-asymmetrical-columns-and-rows-in-power-bi/   but its not the same problem. The total and the departures are not in the same data set in my case.

Note: These are hypothetical examples, and my data sets have more rows.

Thanks.

total population: 

departure data set:

 

Desired result:

 

  

data:

ID	categorie	Department
1011	B	D1
1002	B	D1
1004	C	D2
1011	C	D2
1012	A	D2
1013	A	D1
1014	C	D2
1009	B	D2
1016	B	D2
1017	C	D2
1018	A	D3
1019	C	D2
1020	B	D2
1021	B	D3
1022	B	D3
1023	A	D1

ID	categorie	Department	Leave reason
1001	A	D1	Retirement
1002	B	D1	Lay-off
1003	C	D3	Retirement
1004	C	D2	Resignation
1005	A	D2	Resignation
1006	A	D1	Retirement
1007	C	D2	Lay-off
1008	B	D3	Retirement
1009	B	D2	Resignation
1010	C	D1	Discharge

[Anonymous]

This is awesome!! Thank you for your time!

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, Matrix visual can partially achieved, but if you want to add two list in the matrix column, you should create a new table to custom the matrix. Here's my solution.

1.Create a new table. Don't make relationship with other tables

2.Create a measure.

Measure =
VAR _GrandTotal =
    COUNT ( 'departure data'[ID] )
VAR _TotalPopulation =
    IF (
        ISINSCOPE ( 'departure data'[categorie] ),
        CALCULATE (
            COUNT ( 'total population'[ID] ),
            FILTER (
                'total population',
                'total population'[categorie] = MAX ( 'departure data'[categorie] )
            )
        ),
        COUNT ( 'total population'[ID] )
    )
RETURN
    IF (
        MAX ( 'Table'[Column] )
            IN { "Discharge", "Lay-off", "Resignation", "Retirement" },
        CALCULATE (
            COUNT ( 'departure data'[ID] ),
            FILTER (
                'departure data',
                'departure data'[Leave reason] = MAX ( 'Table'[Column] )
            )
        ),
        SWITCH (
            MAX ( 'Table'[Column] ),
            "Grand Total", _GrandTotal,
            "Total population", _TotalPopulation,
            "%leave rate", FORMAT ( DIVIDE ( _GrandTotal, _TotalPopulation ), "#%" )
        )
    )

Put Department and categorie column from departure data in the Matrix Rows, the new created Column in Matrix Columns, measure in Values, get the expected result:

Notes:

1.To sort in the expected layout, I add an index column in the new table, and sort the new column by Index.

2.I uncheck the Blank option of the new column in the visual filter.

3.In your total population table, Department D3 only contains category A and B, but C is not included.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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