Forum Discussion

Kelz's avatar
Kelz
Icon for Advocate I rankAdvocate I
4 years ago

Help: Traffic Light Staff Capacity formula

Hi,   I am trying to re-create a formula from one Power Bi report (built with Excel spreadsheets) into a new Power Bi (bulit with API's into the data) to show conditonal formatting in colour on a c...
v-robertq-msft's avatar
v-robertq-msft
Icon for Community Support rankCommunity Support
4 years ago

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.

  • Kelz's avatar
    Kelz
    Icon for Advocate I rankAdvocate I
    4 years ago

    Hi Robert, 

     

    Sorry to be a pain, that didnt quite work. 

     

    I need to show the hours the employees completed each month, but coloured coded depending on whether they met or exceeded their hours in the coloumn graph:

     

    ie

    Bob worked 185 hours in October (Sum of timesheet hours - timesheet table)

    Bob's capacity per week is 40 hours (capactity - employee table)

    in the month of October there are 4.29 weeks (weeks in the month - Dates table)

    Bob's expected hours is 4.29 x 40 = 171.60 for the month of October. (capacity x weeks in month)

     

    Free time  = Expected hours - actual hours 

    in bob's case - 171.60 - 185 = -13.4

    If free time is greater than -10 = red (ie they did too much)

    If free time is between 2 & -9 = Green (they worked their hours/ slight over)

    If the free time is less than 2 = Yellow  (they havent done all thier hours)

     

    in the coloumn graph, bob's hours would show red. 

     

    Thanks

     

    Kelz