Having trouble creating a % measure in DAX for use in matrices

[JadeM]

Hello I am trying to make a % using a measure in Power bi but need help as the two parts come from different tables!

I am a Power BI beginner.

The percentage should be holiday / employees - Basically to calculate the percentage of people in the company on holiday each month.

I will use this to make a few matrixes in Power BI. One of % by month by grade, One of % by month by age band and One of % by month by department.

The holiday measure is in the table 'Holiday' value is 'hol'

The total employees is in the table 'Employees' value is 'employees'

There is no relationship between the Holiday and Employees table, and I cannot make one.

• There are indirect relationships via other tables though. Both Holidays and Employees are linked to the table 'Grade' by one ( grade) to many (holiday and employees) relationships.
• Both Holiday and Employees are linked to 'Calendar' by one (calendar) to many (holiday and employees) relationships.
• Both Holiday and Employees are linked to 'department' by many to many relationships.

How can I create the % measure so that I can show the % of people on holiday by the various catergories (age band (which is in holiday table), grade (from grade table) and department (in department table). The matrices will also be broken down by month (using the Calendar table)

I have this sort of idea, but it is not wokring (all cells are blank)

HolidayPercentage =

VAR TotalHolidays = SUM(Holidays[hol])
VAR TotalEmployees = SUM(Employees[employees])



RETURN

DIVIDE(

CALCULATE(

[TotalHolidays],

TREATAS(VALUES(Calendar[Date]), Holidays[Date]),

TREATAS(VALUES(Grade[Grade]), Holidays[Grade]),

TREATAS(VALUES(Department[Department]), Holidays[Department]) ),

CALCULATE(

[TotalEmployees],

TREATAS(VALUES(Calendar[Date]), Employees[Date]),

TREATAS(VALUES(Grade[Grade]), Employees[Grade]),

TREATAS(VALUES(Department[department]), Employees[department]) ), 0 )

Thanks in advance

[v-heq-msft · Accepted solution]

Hi @JadeM ,
According to your description, you want to get the percentage of vacations per month and hie the vacations after each grade are shown by total. Since the data you provided only includes grades, the following calculations are based on grades.

Holiday = SUM('Holiday table'[Hol])

% = 
VAR _totalWorkDays = 
CALCULATE(
    SUM('Employees table'[employee]),
    ALLEXCEPT(
        'Employees table',
        'Employees table'[employee]
    )
)
RETURN
[Holiday]/_totalWorkDays

Final output

Best regards,
Albert He

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

[JadeM]

Hello, here are examples of how the data is set up:

Holiday table:

Hol	Date	Grade
1	01/01/2024	A
0.5	01/01/2024	A
0	01/01/2024	B
0	01/01/2024	C
0	01/01/2024	D
1	01/02/2024	A
0	01/02/2024	A
0	01/02/2024	B
0	01/02/2024	C
0	01/02/2024	D

Employees table:

employee	Date	Grade
1	01/01/2024	A
0.5	01/01/2024	A
0.5	01/01/2024	B
1	01/01/2024	C
1	01/01/2024	D
1	01/02/2024	A
1	01/02/2024	A
1	01/02/2024	B
1	01/02/2024	C
0.5	01/02/2024	D

Grade Table:

Grade	Grade Category
A	Junior
B	Junior
C	Mid
D	Senior

Calendar table:

Date	Month	Year
01/01/2024	January	2024
01/02/2024	February	2024

* the 'hol' and 'employees' column basically show the full time equivilent - so 1 = one full time person and 0.5 = one person working half the time. There are other columns in the table that I have not included.

How I would like the matrix to look:

Grade	Holiday	%	Holiday	%
A	1.5	100%	1	50%
B	0	0%	0	0%
C	0	0%	0	0%
D	0	0%	0	0%
Total	1.5	30%	1	20%

I can get the 'total' % right, just not the other % (in this case the 100% and 50%) instead the %s shown in the matrix are based total number of employees - rather than the total of each grade

Thank you

[vanessafvg]

sorrry this format is really not easy to work with  can you not provide a cloud link to an excel file /csv copy and pasting this into power bi the format is all messsy. preferable to give it in a good format if you need assistance, or put it in excel and copy and paste from there

[vanessafvg]

can you provide some sample data and what your expected result it.   Hard to test a  solution without knowing what correct looks like.