Forum Discussion
Count and Conditional formatting by cell values
Hello PB Community
What I am trying to achieve based on the name and projectcode is how many employees each week and each month cover the set listed below requirements.
1. The number of people booking time (Full) - Need to know the list of names [in a table] and total count where their working hours were above the target hours by country and product code. For ex. The US the target hours was 176 mentioned in the reference table
2. Who has not booked any time for a week - Need to know the list of names [in a table] and count where their working hours was "0" by country and product code
3. Who has not booked full time for a month - Need to know the list of names [in a table] and count where their working hours was less than target monthly hours by country and product code. For ex. The US the target hour was 176
4. Booking status (traffic light) for each individual (fully/partially/not) - Set conditional formatting by highlighting [GREEN] who has achieved the target and above, partial [YELLOW] below and average and lastly [RED] not met the target
Any help is much appreciated.
Anonymous
Hi again,
I apologise upfront since I hadn't considered the impact of 0 values, except for the visual you requested to show the count of names with 0 in any week. And of course, it also has an impact (potentially) in the monthly calculations. A big oversight of mine.
I have therefore re-worked through most of the measures to include names with 0 bookings in the overall calculations, including the count of those below the expected target (I have highlighted them in the tables). I'm afraid you will have to labour through the changes to adapt them to your exact model. Also please check out the filters applied to each visual in the filter pane.
There is one imortant point about the calculations. The model presently only considers 0 values if the value in the table is an actual 0. If there is no data present for a particular week, it does not compute that particluar User ID.
So based on the sample data there are 3 Users with 0 values included.
On the other hand, Richard ID 10 only appears in week 1 2018 (there are no values in 2017. Therefore there no records or listings for this user prior to 2018 Week 1).
In effect, the interpretation is that if there is no value, that user wasn't operating. IF that user was operating, we would expect at least a 0 as a value. Does that make sense?
I do have another version which will compute a 0 value for all IDs when there are no records, but this would deliver data potentially for IDs who have not even started operating. The ideal situation would of course be to have an operating start/end week/year for each ID (HR table) which we can then use to filter appropriately. For the time being, I've assumed that if there is no data for a given User ID, that user was not operating neither bookings nor target).
If you would like the version which computes all User IDs in all periods, do let me know and i'll post it for you.
The easiest way to control this phenomenom is if there are fields in the HR Table which establish an activity start or end year/week. These time frames can then be used to filter the data accordingly. Just bear this in mind.
Anyway, here are some resulting screenshots:
And here is the link to the file:
21 Replies
Anonymous
Hi again. This is where I have got so far.
I've built the model like this:
For Question 1:
For Question 2:
For Question 3:
For Question 4 (I'm not sure what criteria you wish to apply for Yellow):
And here is the PBIX file:
Many Thanks Paul, really appreciate your reply, exactly this is the view I'm after. The solution you provide is working perfectly fine on small datasets, but when I start applying to large datasets and connecting with HR datasets, some of the views are throwing error. Maybe I assume the in my earlier dataset I combine the table for convenience purposes, which might be the case and so when i flatten the table, relationship not working. Again this is my fault for not normalising the file.
For e.g. following error is visible like:-
- The sum monthly target hours coming the same for all the countries.
- The other issue we have with the same name but these different people with a unique ID (for e.g. Richard with ID 10 - newly added), due to which it is showing Many to Many relationships
- In future the HR data is coming from HR Software, my worry is if we flatten the table and create individual tables for Name, Product, Country, what if we have to add new rows how that new data sync with these individual table
I've recreated the datasets again. Any help will be grateful. I've attached the new datasets with 3 tables
- Main Table (User_ID replaced the Name) - Coming from Time tracking software
USER_ID ProjectCode BookingYear BookingMonth BookingWeek Booked_Hours Customer 1 C 2017 12 52 240 GEO 2 C 2017 12 52 120 GEO 3 M 2017 12 52 510 KLL 4 M 2017 12 52 15 KLL 5 K 2017 12 52 60 FOX 6 K 2017 12 52 30 FOX 7 K 2017 12 52 72 HTT 8 U 2017 12 52 6 KBA 9 U 2017 12 52 510 KBC 2 C 2018 1 1 0 GEO 1 C 2018 1 1 24 GEO 3 M 2018 1 1 24 KLL 4 M 2018 1 1 0 KLL 5 K 2018 1 1 24 FOX 6 K 2018 1 1 18 FOX 7 K 2018 1 1 43 HTT 8 U 2018 1 1 111 KBA 9 U 2018 1 1 0 KBC 10 C 2018 1 1 100 GEO 2. CountryHour (No changes) Standalone
COUNTRY Base Hours Expected Weekly Hours Working Days Target Hours US 8 40 22 176 UK 8 40 22 176 CHINA 8 40 23 184 MEXICO 7.5 37.5 21 157.5 3. HR Data (Added a new name with Unique ID) Coming from HR software
Name USER_ID Manager Country Peter 1 Jerry CHINA Sodhi 2 Jerry CHINA Sodhi 2 Jerry CHINA Peter 1 Jerry CHINA Richard 10 Jerry CHINA Emmanuel 3 Naple MEXICO Philip 4 Naple MEXICO Emmanuel 3 Naple MEXICO Philip 4 Naple MEXICO Henry 5 Simon UK Mary 6 Simon UK Richard 7 Simon UK Henry 5 Simon UK Mary 6 Simon UK Richard 7 Simon UK Patel 8 Martin US John 9 Martin US Patel 8 Martin US John 9 Martin US Hi,
What exact result are you expecting?
Anonymous
Can you please attach the data (I take it each image corresponds to the tables you mention) Either as a file (shared from Onedrive, Dropbox, Google Drive...) or by copying and pasting in the "table" option included in the message options ribbon (see image attached)?
Anonymous
Hi, can you please clarify the following:
1) what does the HourPerMonth Column show exactly? The granularity at each row is "week" yet the column header states "HourPerMonth" - I take it this is the column which reflects the number of hours each person has "Booked" (ie. the values we are comparing with the targets)
2) I also see that "Emmanuel" is listed in several rows with different "HourPerMonth" values for the same ProjectCode/Year/Month/Week/Customer. Is that correct?
3) I also see that ProjectCode "K" has two different Customers (FOX & HTT) Is that correct?
Thanks.
