Matrix and percentage of total for two categories

[Anonymous]

How can I create two tables like this (data below) for each categorie and department:

%departure is grand total/total population

Table 1

Table 2:

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

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

[v-jianboli-msft]

Hi @Anonymous ,

Please try:

First create a new column:

Table 3 =
VAR _a =
    ADDCOLUMNS (
        CROSSJOIN (
            SELECTCOLUMNS (
                'Table 2',
                "ID", [ID],
                "categorie", [categorie],
                "Department", [Department]
            ),
            { "Grand Total" }
        ),
        "Sort", 5
    )
VAR _b =
    ADDCOLUMNS ( CROSSJOIN ( 'Table 1', { "Total population" } ), "Sort", 6 )
VAR _c =
    UNION (
        _a,
        _b,
        ADDCOLUMNS (
            'Table 2',
            "Sort",
                SWITCH (
                    [Leave reason],
                    "Discharge", 1,
                    "Lay-off", 2,
                    "Resignation", 3,
                    "Retirement", 4
                )
        )
    )
RETURN
    _c

Then sort the value by column [Sort]:

Then create a measure and apply it to the matrix visual:

Measure =
VAR _a =
    CALCULATE (
        COUNT ( 'Table 3'[ID] ),
        FILTER ( 'Table 3', [Value] = "Grand Total" )
    )
VAR _b =
    CALCULATE (
        COUNT ( 'Table 3'[ID] ),
        FILTER ( 'Table 3', [Value] = "Total population" )
    )
VAR _c =
    FORMAT ( DIVIDE ( _a, _b ), "Percent" )
RETURN
    IF ( ISINSCOPE ( 'Table 3'[Value] ), COUNT ( 'Table 3'[ID] ), _c )

Change the name of the visual's subtotal:

Final output:

Best Regards,

Jianbo Li

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

[Anonymous]

Thank you so much! When I try to create the table with the first measure I get this error message however: Each table argument of 'UNION' must have the same number of columns.

In reality my Table 1 and 2 have a lot more columns. I changed table 1 to select the same columns as 2, but I still get the message. I found that it is because of table 2, it has more columns. How can I add selectcolumns statement in the below to select the same columns?

ADDCOLUMNS (
            'Table 2',
            "Sort",
                SWITCH (
                    [Leave reason],
                    "Discharge", 1,
                    "Lay-off", 2,
                    "Resignation", 3,
                    "Retirement", 4

Thanks. 

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Please try to use the function SELECTCOLUMNS().

It returns a table with selected columns from the table and new columns specified by the DAX expressions.

For more details, please refer to:

SELECTCOLUMNS function (DAX) - DAX | Microsoft Learn

Here is the DAX:

Table 3 = 
VAR _a =
    ADDCOLUMNS (
        CROSSJOIN (
            SELECTCOLUMNS (
                'Table 2',
                "ID", [ID],
                "categorie", [categorie],
                "Department", [Department]
            ),
            { "Grand Total" }
        ),
        "Sort", 5
    )
VAR _b =
    ADDCOLUMNS ( CROSSJOIN ( 'Table 1', { "Total population" } ), "Sort", 6 )
VAR _c =
    UNION (
        _a,
        _b,
        ADDCOLUMNS (
            SELECTCOLUMNS('Table 2',"ID",[ID],"categorie",[categorie],"Department",[Department],"Leave reason",[Leave reason]),
            "Sort",
                SWITCH (
                    [Leave reason],
                    "Discharge", 1,
                    "Lay-off", 2,
                    "Resignation", 3,
                    "Retirement", 4
                )
        )
    )
RETURN
    _c

Output:

Best Regards,

Jianbo Li

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