cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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.

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.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors