cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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(Department[Department]), Holidays[Department]) ),

CALCULATE(

[TotalEmployees],

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

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

1 ACCEPTED SOLUTION
Community Support

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

4 REPLIES 4
Community Support

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

Frequent Visitor

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

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

Super User

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

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Super User

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

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors