Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kelz
Advocate I
Advocate I

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 column chart and its not working.

 

Orignially, when recreating the formula it had an error that one of my cells was text but, now my data is all numbers and its still not showing the way I want. 

 

The data I want to show is a traffic light on the amount of hours a staff member has done in the month vs their individual capacity. ie Bob is expected to do 37.5 hours per week, the month of July 2021 has 4.29 weeks in the month, Bobs expected hours for July is 160.87 hours, Bob has actually worked 180 hours, which is 19.13 over his expected and needs to be shown as red.

 

The formulas used are

 

Weeks in Month = DATEDIFF(Dates[SOM], Dates[EOM], DAY) / 7

 

Capacity = SUMX('Harvest Employee', 'Harvest Employee'[Capacity] * SUMX(FILTER(Dates, Dates[Date] = Dates[SOM]), Dates[Weeks in Month]))
 
Free Time = [Capacity] - SUM('All data'[Hours])

 

the orignal graph appears as:

 

Kelz_0-1635752488035.png

 

I have the same formulas in my new model, but the graph appears as: 

 

Kelz_1-1635753043839.png

 

My data colour formmatting is:

 

Kelz_2-1635753084355.png

My new model has the formulas:

 

Weeks in the month = Dates[Days in Month]/7
 
Monthly Capacity = sumx('Harvest - Users' , 'Harvest - Users'[weekly_capacity] * sumx(filter(Dates, Dates[Date] = Dates[SOM]), Dates[Weeks in the month]))

 

Free Time = [Monthly Capacity] - SUM('Project Data'[Hours])
 
My data has :
 
Table: Users - Employee & expected hours per week
Table: Timesheet - Employee & hours worked  (muilptle lines with different descriptions/hours for each project/task.
Table: Date's
 
Can somebody shed any light on why my formula is not working and/or how I fix it. 
 
Thanks
 
Kelz
 

 

13 REPLIES 13
v-robertq-msft
Community Support
Community Support

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")

vrobertqmsft_0-1636702418197.png

 

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 Robert, 

 

sorry it didnt work. I dont want to show the "Free Time". I want to show the amount of hours that the staff member has done, and I want the color to be based on whether they have done their hours or not. I

also have a splicer on the dates, so I want it to change based on whatever month is choosen

 

Kelz_0-1637260087567.png

 

Kelz_1-1637261390066.png

 

Cheers

 

Kelz

 

 

Hi @Kelz - Did you mark this as solved? I see it is marked as solved but your latest question came in after the "solution".

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Hi, no I didn't, I think the system may have? or somebody might have if they can do that? and Im unsure how to revert it.

 

The instructions Robert had were great and work well if I wanted to show the free time, but I wanted to show the hours the staff had worked for the month with the colour for each staff member based on whether they were under, over or at their contracted hours for month/s selected (I have a slicer on months and muiltple can be selected). 

 

the original formula worked really well for the model I created using excel reports, but I've had to recreate the report using an API, so the names / tables are slightly different but still has the same information. 

 

Thanks

Kelly

@Kelz , @KNP , I have unmarked the "Accepted Solution"  as it looks as @Kelz  has not found the correct answer to their question.  If I am wrong, please advise.

 

Thank you for being part of the community!

 

cc: @v-robertq-msft@KellyK@chass 

v-robertq-msft
Community Support
Community Support

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:

vrobertqmsft_0-1636528089303.png

vrobertqmsft_1-1636528089311.png

 

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 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

v-robertq-msft
Community Support
Community Support

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:

vrobertqmsft_0-1635933622091.png

 

And you can get what you want, like this:

vrobertqmsft_1-1635933622124.png

 

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, 

 

Thanks for that, it didnt work, but i think it might be because I need to link it to the indivdual/unique staff members. 

 

ie bob is 37.5, sally is 20 hours, jane is 30 hours which is under the employee tab. 

 

do you know how I can get the free time formula to filter on each of the staff members capacity and not the whole group? 

 

thanks 

 

kelz

PREVIEW
 
 
 

Hi, 

According to your description, I can roughly understand what you want to get. But I find it hard to create the test data based on your requirement details. Would you like to post some sample data in table form or pbix file(without sensitive data) and your expected result(like the chart you want to get and the correct measure value based on your sample data)?

Thanks very much!

How to Get Your Question Answered Quickly 

 

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, 

 

Here is the table examples

 

Timesheet data

DatehoursEmployeeProject
1/10/20214Bob Project 1
1/10/20212SallyProject 3
1/10/20214.5JoProject 5
1/10/20215RandyProject 1
1/10/20213.75SandyProject 3
1/10/20213TinaProject 5
1/10/20214TimProject 1
1/10/20214RogerProject 3
1/10/20214Bob Project 1
1/10/20212SallyProject 3
1/10/20214.5JoProject 5
1/10/20215RandyProject 1
1/10/20213.75SandyProject 3
1/10/20213TinaProject 5
1/10/20214TimProject 1
1/10/20214RogerProject 3
4/10/20214Bob Project 1
4/10/20212SallyProject 3
4/10/20214.5JoProject 5
4/10/20215RandyProject 1
4/10/20213.75SandyProject 3
4/10/20213TinaProject 5
4/10/20214TimProject 1
4/10/20214RogerProject 3

 

staff table:

EmployeeCapacity
Bob 40
Sally20
Jo45
Randy50
Sandy37.5
Tina30
Tim40
Roger40

 

Thanks 🙂

 

amitchandak
Super User
Super User

@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

Hi @amitchandak 

 

Thanks for the suggestion, I tried it, but when changing it to a number it comes up as a red box 

 

Kelz_0-1635787151085.png

and that color formatting works in my old model 

 

Cheers, 

 

Kelz

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors