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:
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]))
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.
2. 2-Count People Below Target =
3. 2-0 hours in a week =
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 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 .2. Row subtotal is not coming correctly for above and below target (circled in the insert diagram)
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)])))))