Help: Traffic Light Staff Capacity formula

Hi, Kelz 

According to your description and sample pictures, I can roughly understand your requirement, I think you can try to create a measure to define the data color manually instead set it in the color formatting, you can try this measure:

Color =

SWITCH(

    TRUE(),

    [Free Time]<-10,"Red",

    [Free Time]>=-9&&[Free Time]<1,"Yellow",

    [Free Time]>1&&[Free Time]<=1000,"Green")

Then go to the data color setting of this column chart, set like this:

And you can get what you want, like this:

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, 

According to your description and sample picture, I think you can try to use these measures to create a column chart to achieve your requirement:

Free Time =

var _sum=CALCULATE(SUM('Timesheet data'[hours]),FILTER(ALL('Timesheet data'),[Employee]=MAX('staff table'[Employee])))

return

SUM([Capacity])-_sum

Color =

SWITCH(

    TRUE(),

    [Free Time]<-10,"Red",

    [Free Time]>=-9&&[Free Time]<1,"Yellow",

    [Free Time]>1&&[Free Time]<=1000,"Green")

And you can create a column chart to set the data color like this to get what you want:

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, 

According to your description, I think you can make some changes on the measure [Free time] to get the requirement you wanted:

Free Time =

var _date=EOMONTH(TODAY(),-1)

var _weeks=divide(DATEDIFF(DATE(YEAR(_date),MONTH(_date),1),EOMONTH(_date,0),day),7)

var _sum=CALCULATE(SUM('Timesheet data'[hours]),FILTER(ALL('Timesheet data'),[Employee]=MAX('Timesheet data'[Employee])))

return

MAX('staff table'[Capacity])*_weeks-_sum

You can define the first variable _date according to your needs, in this case, is the last date in October

Then you can do the same thing as my above replies to achieve your needs:

Color =

SWITCH(

    TRUE(),

    [Free Time]<-10,"Red",

    [Free Time]>=-9&&[Free Time]<1,"Yellow",

    [Free Time]>1&&[Free Time]<=1000,"Green")

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Kelz , I think 1st and 3rd condition has a problem. Reconsider and correct. Alos you should number at all place, you are using percent in first one