topic Re: Help: Traffic Light Staff Capacity formula in DAX Commands and Tips

Help: Traffic Light Staff Capacity formula

Kelz — Mon, 01 Nov 2021 07:59:37 GMT

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:

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

My data colour formmatting is:

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

Re: Help: Traffic Light Staff Capacity formula

amitchandak — Mon, 01 Nov 2021 09:17:40 GMT

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

Re: Help: Traffic Light Staff Capacity formula

Kelz — Mon, 01 Nov 2021 17:20:38 GMT

Hi amitchandak

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

and that color formatting works in my old model

Cheers,

Kelz

Re: Help: Traffic Light Staff Capacity formula

v-robertq-msft — Wed, 03 Nov 2021 10:03:02 GMT

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.

Re: Help: Traffic Light Staff Capacity formula

Kelz — Sun, 07 Nov 2021 08:02:59 GMT

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

Re: Help: Traffic Light Staff Capacity formula

v-robertq-msft — Mon, 08 Nov 2021 02:08:14 GMT

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.

Re: Help: Traffic Light Staff Capacity formula

Kelz — Wed, 10 Nov 2021 01:12:30 GMT

Hi,

Here is the table examples

Timesheet data

Date	hours	Employee	Project
1/10/2021	4	Bob	Project 1
1/10/2021	2	Sally	Project 3
1/10/2021	4.5	Jo	Project 5
1/10/2021	5	Randy	Project 1
1/10/2021	3.75	Sandy	Project 3
1/10/2021	3	Tina	Project 5
1/10/2021	4	Tim	Project 1
1/10/2021	4	Roger	Project 3
1/10/2021	4	Bob	Project 1
1/10/2021	2	Sally	Project 3
1/10/2021	4.5	Jo	Project 5
1/10/2021	5	Randy	Project 1
1/10/2021	3.75	Sandy	Project 3
1/10/2021	3	Tina	Project 5
1/10/2021	4	Tim	Project 1
1/10/2021	4	Roger	Project 3
4/10/2021	4	Bob	Project 1
4/10/2021	2	Sally	Project 3
4/10/2021	4.5	Jo	Project 5
4/10/2021	5	Randy	Project 1
4/10/2021	3.75	Sandy	Project 3
4/10/2021	3	Tina	Project 5
4/10/2021	4	Tim	Project 1
4/10/2021	4	Roger	Project 3

staff table:

Employee	Capacity
Bob	40
Sally	20
Jo	45
Randy	50
Sandy	37.5
Tina	30
Tim	40
Roger	40

Thanks 🙂

Re: Help: Traffic Light Staff Capacity formula

v-robertq-msft — Wed, 10 Nov 2021 07:09:27 GMT

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.

Re: Help: Traffic Light Staff Capacity formula

Kelz — Thu, 11 Nov 2021 18:06:16 GMT

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:

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

Re: Help: Traffic Light Staff Capacity formula

v-robertq-msft — Fri, 12 Nov 2021 07:34:26 GMT

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.

Re: Help: Traffic Light Staff Capacity formula

Kelz — Thu, 18 Nov 2021 18:50:32 GMT

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

Cheers

Kelz

Re: Help: Traffic Light Staff Capacity formula

KNP — Wed, 01 Dec 2021 19:53:20 GMT

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

Re: Help: Traffic Light Staff Capacity formula

Kelz — Wed, 01 Dec 2021 20:05:38 GMT

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

Re: Help: Traffic Light Staff Capacity formula

Anonymous — Wed, 01 Dec 2021 21:48:16 GMT

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, Anonymous, chass