cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PeteGen Frequent Visitor
Frequent Visitor

Using DAX to show when an employee has greater allocation then availability

Hi everyone,

 

I'm brand new to Power BI, DAX and the Community 🙂

 

I've been tasked with creating a Matrix that shows a Resoure(employees name), the 1st of each month and how much work an employee has been allocated. The Matrix also has to show when a Resource has been allocated more than there availability(part-time, full-time, etc) by colouring a cell red. 

 

Below is the fake data I created to work with.

Projects.PNG

 

I would like to create a caluculated column and then reference the calulated column in the matrix conditional formatting so the colour red will show up for employees that have more allocation then avaliability.

 

However, I'm struggling to create this in DAX since a person can be working on more than one project at a time and projects may have different start and end dates. 

 

Please note in the real model the above table has been created using a cross join but the fake data I've created has the same column names. Unfortuantely due to the data at work having real peoples names, I will not be posting the actually dataset.

 

The link below is to the Power BI report

https://1drv.ms/u/s!ApYdg6ECJ7QzbnpwM20cR6nuGqE

 

I'm looking forward to hear peoples' ideas 🙂

 

2 REPLIES 2
Super User I
Super User I

Re: Using DAX to show when an employee has greater allocation then availability

Could you do a quick mock-up, based on the data above, what you would like the output to be?

Highlighted
PeteGen Frequent Visitor
Frequent Visitor

Re: Using DAX to show when an employee has greater allocation then availability

Hi @Nick_M thanks for your reply 🙂

 

For example, if Jack has been allocated 0.3% to a project but only has avaliblity of 0.2% I want him to show up in red in the Matrix as show below.Red.PNG

This will indicate that Jack has been allocated too much and can not be allocated anymore. The output I wish to show is that any employee that has a greater allocation then availability to show up as red.

 

I got Jack to show up as red by adding a calculated column in my projects table using a switch statement saying if %allocation is greater then availability then colour red. As shown below.Switch.PNG

 

 

Below is the link to the Power BI file as I have made some changes to the end dates.

https://1drv.ms/u/s!ApYdg6ECJ7QzbnpwM20cR6nuGqE

 

Kind Regards,

 

PeteGen

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors