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

[PeteGen]

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.

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 🙂

[Anonymous]

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

[PeteGen]

Hi @Anonymous 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.

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.

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