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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Count and Conditional formatting by cell values

Hello PB Community

 

I just need some help organizing how to do this.  I have a 2 tables one displays the names, projectcode, year working week, hours" and the other one is reference table with country and target hours. 
 

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.

 
Many Thanks
 
File attached
I st Table
 
2nd Table - reference table
 
 
1 ACCEPTED SOLUTION

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

 

Full v4.JPG

 

Below v4.JPG

 

0 hours v4.JPG

 

 

And here is the link to the file:

Counting Booking Hours V4 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

21 REPLIES 21
PaulDBrown
Community Champion
Community Champion

@Anonymous 

 

Hi again. This is where I have got so far.

 

I've built the model like this:

Model.JPG

 

For Question 1:Above.JPG

 

For Question 2:

0 hours.JPG

 

For Question 3:

Below.JPG

 

For Question 4 (I'm not sure what criteria you wish to apply for Yellow):

Full.JPG

 

And here is the PBIX file:

Booked hours PBIX





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

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

 

  1. The sum monthly target hours coming the same for all the countries. 
  2. 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
  3. 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

 

  1. Main Table (User_ID replaced the Name) - Coming from Time tracking software
USER_IDProjectCodeBookingYearBookingMonthBookingWeekBooked_HoursCustomer 
1C20171252240GEO 
2C20171252120GEO 
3M20171252510KLL 
4M2017125215KLL 
5K2017125260FOX 
6K2017125230FOX 
7K2017125272HTT 
8U201712526KBA 
9U20171252510KBC 
2C2018110GEO 
1C20181124GEO 
3M20181124KLL 
4M2018110KLL 
5K20181124FOX 
6K20181118FOX 
7K20181143HTT 
8U201811111KBA 
9U2018110KBC 
10C201811100GEO 

 

2. CountryHour (No changes) Standalone

 

COUNTRYBase HoursExpected Weekly HoursWorking DaysTarget Hours
US84022176
UK84022176
CHINA84023184
MEXICO7.537.521157.5

 

3. HR Data (Added a new name with Unique ID) Coming from HR software

 

NameUSER_IDManagerCountry
Peter1JerryCHINA
Sodhi2JerryCHINA
Sodhi2JerryCHINA
Peter1JerryCHINA
Richard10JerryCHINA
Emmanuel3NapleMEXICO
Philip4NapleMEXICO
Emmanuel3NapleMEXICO
Philip4NapleMEXICO
Henry5SimonUK
Mary6SimonUK
Richard7SimonUK
Henry5SimonUK
Mary6SimonUK
Richard7SimonUK
Patel8MartinUS
John9MartinUS
Patel8MartinUS
John9MartinUS

 

Hi,

What exact result are you expecting?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thanks Ashish

 

What I am trying to achieve based on these three datasets 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 80%, partial [YELLOW] between [79% to 30%] and lastly [RED] not meeting the target [between 29% to 0%]
 
Appreciate your help.
 
Many Thanks

@Anonymous 

I see, yes, if the structure is different, we need to tweak the model and a couple of measures. See if this works for you:

First the structure of the model with the new HR Table.

Model v2.JPG

 

Please note that I have converted the HR Table into a lookup table (no duplicate values) on import.

 

And with a couple of tweaks to the measures to adjust for the new table, data structure etc, we get the following.

Answer question 1: People above the target

Above v2.JPG

 

Answer question 2: people booking 0 hours (please note that newcomer Richard has no data for 2017 (ie no value at all, either 0 or otherwise - it looks like he joined in 2018?)

0 hours v2.JPG

 

Answer Question 3: not booked full time in a month

Below v2.JPG

 

Answer question 4: Traffic Light Table

Fullv2.JPG

 

And here is the PBIX file:

Counting Working Hours V2 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

Thanks Paul once again for your swift reply. Seems all working, except my Sum Monthly Target hours column which is giving wrong results (attached), all the values are coming same. I checked the relationship, they seem okay (attached below). Don't know what went wrong.

 

What I'm doing is replicating your model with my Org data. Since the time tracking data is connected with Power BI real-time, so can't tweak, Two other files imported into PBI - HRData and Country Hour. I'm currently focusing on metrics by Name and Country, not by ProjectCode and Customer(which in my CDM is BookingKey & Summary). DateTable is there, not currently using, instead, I'm using the Booking hours and Booking month, Week from my HoursPax table.

 

Don't know where I'm going wrong?

CDM.jpgError_1.jpg

Anonymous
Not applicable

Sum Monthly Target Hours =
CALCULATE(SUM(CountryHours[Total Hours]),
ALLEXCEPT(Country,Country[Country]),
RELATEDTABLE(Country))

@Anonymous 

The measure looks fine. What are the other fields in the visual which renders the same value on every line for the measure?

 

If you don't have "Country" in the rows to create the filter contex you might be getting wrong values:

country filter context.JPG

 

Do you need a measure to calculate the target working hours by Name (only)?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

FieldList.jpg

 

All field are coming from same Table "HoursPAX". KnownAs is Name field

@Anonymous 

 

OK, can you try this measure?: (Try it using the "Name" field from your HR Table instead of the name field from your HoursPax table)

Target Hours by Name = CALCULATE(SUM(CountryHours[Monthly Target Hours]); 
                        TREATAS(VALUES(HRTable[Country]); 'Country Table'[Country]))

 

Target Hours by Name.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

Many Thanks Paul - Great support

The newly created DAX is working, answering to your earlier question the field "Country" and "Name" I added from talent table by using Related - Which I deleted now.

Struck again spotted an error with below mentioned DAX measures. Currently, the datasets got a total distinct User_ID of 2500 +, whereas when we use these two measures for count people above target and below target, the result coming as 25 (above Target) and 522 (below target). It seems not correct. Can you please revisit the below DAX and suggest accordingly. Appreciate your commitment and support.

 

1. 2-Count People Above Target =
VAR CALC = SUMMARIZE(HoursPax , HoursPax [YEARMONTH], HoursPax [USERNAME], HoursPax [BOOKINGKEY], "Diff vs Target" , [Diff Booked vs Monthly Hour Target])
RETURN
CALCULATE(DISTINCTCOUNT(HoursPax [USERNAME]),
FILTER(CALC, [Diff vs Target] > 0))

 

2.  2-Count People Below Target =

VAR calc = SUMMARIZE(HoursPax , HoursPax [YEARMONTH], HoursPax [USERNAME], HoursPax [BOOKINGKEY], "Diff vs Target" , [Diff Booked vs Monthly Hour Target])
RETURN
CALCULATE(DISTINCTCOUNT(HoursPax [USERNAME]),
FILTER(calc, [Diff vs Target] < 0 && [Sum Hours (total)] >0))
 

3. 2-0 hours in a week =

VAR allcal = CALCULATE([Sum Hours], ALL(HoursPax )
Return
IF(ISBLANK(allcal), BLANK(),IF(ISBLANK([Sum Hours]),1,
IF( [Sum Hours]>0, 2, 1)))
 
4. 2-People with 0 hours in a week = [I think it is more than 500]
 
CALCULATE(DISTINCTCOUNT(HRTable[Known as]),
FILTER(HoursPax ,
[2-0 hours in a week] = 1))

Error_2.jpgError_3.jpgError_Measures.jpg

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

 

Full v4.JPG

 

Below v4.JPG

 

0 hours v4.JPG

 

 

And here is the link to the file:

Counting Booking Hours V4 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

Thanks once again. Much appreciated.

Having used the new set of DAX measures which is so far working fine, but still, there are some minor errors which I've listed in the attached format with little explanation.

1. % Results vs Target variance is not correct (as stated below), I think it is using fixed target hours in the Country hours reference table. But when we drill down by name the variance is coming correctly- Is there a way to fix . 

Error_4.jpg

 

2. Row subtotal is not coming correctly for above and below target (circled in the insert diagram)

 

Error_6.jpgError_8.jpg

3. Pax with 0 hours in week - Seems there is an error in the DAX, beacause when i choose HoursPAX [Book] = 0, showing no value in the table, but when i remove 0, then i can see the table with incorrect calculation. So without 0 the table is counting the value 1, but with 0 (as stated in your DAX), Table is Blank.

 

 = COUNTROWS(
CALCULATETABLE(VALUES(Talent[Known as]),
FILTER(ALL(Talent[USERNAME]),
SUM(HrsPAX[BOOKED_MINS])/60 
&& NOT(ISBLANK([Sum Hours (total)])))
))

Error_7.jpg

 

 

 

 

 

 

@Anonymous 

 

I'm confused. My HoursPax table only has a UserID field (and I see the field you have int your matrix "Known as" comes from your HR table. My HoursPax table doesn't have a country field either. What are the fields linking your HoursPax table to your HR table?

I have just recreated the matrix visual using fields form the HoursPax table (except for the "country" which isn't in my HoursPax table (used the Country table field instead, and it still works.

HoursPax.JPG

 

My HoursPax table is linked to the HR Table by the UserID field.

 

Inked2020-01-28_LI.jpg

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@Anonymous 

 

Can you please post the [Sum Monthly Target hours] measure?

Thanks,

Paul





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






PaulDBrown
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

Paul

 

You correctly spooted some data quality error, i made some changes and attached the excel again, hope this should be okay now. 

Anonymous
Not applicable

 
PaulDBrown
Community Champion
Community Champion

@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)?

20200126_144906904_iOS.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors