In the attached workbook, I am trying to get a % of officers that went to the office at least three times in a week. I have a calculated field that has this formula:
Officer % = sum({Fixed Week([Date]), [Cardholder Name]:[Entry Count]}) /
sum({Fixed Week([Date]), [Manager]:MIN([Direct Reports])})
The formula works when one of the Managers is selected in the filter, but if "All" is selected, the % are wrong. I believe what is happening is if none of the officers under a specific manager come in at least 3 times that week, it is subtracting the group of officers from the denominator.
What I am hoping to do is if "All" is selected the total number of officers should be 78. If anyone can help me out, that would be great!
Hi @Ayinde Hammed . Thanks for the reply, yes this calculation is very confusing and there is alot of background info to the data. However, I was able to resolve it, but thank you for taking the time to reply!
