Forum Discussion
Count and Conditional formatting by cell values
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:
Thanks Ashish
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
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%]
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.
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
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?)
Answer Question 3: not booked full time in a month
Answer question 4: Traffic Light Table
And here is the PBIX file:
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?
Anonymous
Can you please post the [Sum Monthly Target hours] measure?
Thanks,
Paul
- 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:
Do you need a measure to calculate the target working hours by Name (only)?