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

Highlighting red for oversubscribed and green for under subscribed

Hi everyone,

I've created a Matrix that shows a Resource(employees name), the date and how much work an employee has been allocated. 

However, the Matrix also needs to be colour coded to show when a Resource has been allocated more than their availability (part-time, full-time, etc) and when an employee has not been allocated enough work. (Red for overworked and green for underworked).

I'm struggling to do this since a person can be working on more than one project at a time and projects have different start and end dates. 

Below is some sample data that I've been using. Please note in the real model the table below was created using a CrossJoin.

Green.PNG

 

Below is the desired output but also needs to include the colour green.

 

Red.PNG

Hope the above all made sense 🙂

Below is a link to the Power BI report

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

 

Looking forward to any advice.

 

Regards,

PeteGen

 

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Highlighting red for oversubscribed and green for under subscribed

Hi @PeteGen 

Create a measure

Measure = IF(MAX(Projects[Allocation])>MAX(Projects[Availability ]),1,IF(MAX(Projects[Allocation])<MAX(Projects[Availability ]),0))

Remove all conditional formatting,

then add new conditional formatting.

1.png2.png3.png

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
PeteGen Frequent Visitor
Frequent Visitor

Re: Highlighting red for oversubscribed and green for under subscribed

Hi @ thanks for your reply. I tried out your solution and had moderate success. The problem I’m facing is that someone maybe working fulltime, partime, etc with the Availability of 1, 0.8, etc but may have been allocated portions of different projects which is greater than their availability.

 

For example, Nick has the availability of 0.8

And has been allocated 0.3 to a MultiMedia Project which starts on 17/03/2019 and ends on the 25/06/2019.

Nick has also been allocated 0.5 to a WoodWork project which starts on 19/05/2019 and ends on the 14/07/2019.

Nick has also been allocated 0.4 to a MetalWork project which starts on 2/06/2019 and ends on the 26/09/2019.

 

So, Nick has a total of 1.2 allocated to him which is above his 0.8 availability. As his WoodWork project starts before the MultiMedia project has finished, same with the MetalWork project starting before the WoodWork project has finished. Even when one project finishes Nick’s allocation is either equal to his availability or just over.

 

 

 

When I tried your solution Nick would come up is under allocated since it was only looking at one date. Ideally the dates of the matrix should be the first of each month instead of each day.

 

I've created a new Power BI report with data which is very similar to what I'm using at work. As I feel that the previous Power BI report I posted lacked detail. 

 

The Power BI link is below:

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

 

I’m looking forward to hearing from you 🙂

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

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 BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors