Hello, I've frequently used other users questions and finally I have come up against something that I can't find an answer to.
I would like to create a table that has data buckets on the left side and across the top. I would like to be able to count the number of employees that fall into the combination of the two groups. Specifically I want to know how employees have used 'special PTO' and 'regular PTO'. I have set up data buckets in seperate tables for each 'special PTO' and 'regular PTO'.
I have successfully counted the number of employees in each bucket/PTO type using the following measures:
Number of Associates in Regular PTO Groupings = IF(HASONEVALUE('Regular PTO Buckets'[MinRangeValue]),COUNTROWS(FILTER('Time-Off Bank Report', 'Time-Off Bank Report'[PTO Sum] >= VALUES('Regular PTO Buckets'[MinRangeValue]) && [PTO Sum] < VALUES('Regular PTO Buckets'[MaxRangeValue]))),COUNTROWS('Time-Off Bank Report'))
&
Number of Associates in Special PTO Groupings = IF(HASONEVALUE('Special PTO Buckets'[MinRangeValue]),COUNTROWS(FILTER('Time-Off Bank Report', 'Time-Off Bank Report'[Special PTO Sum] >= VALUES('Special PTO Buckets'[MinRangeValue]) && [Special PTO Sum] < VALUES('Special PTO Buckets'[MaxRangeValue]))),COUNTROWS('Time-Off Bank Report'))
Both Regular and Special PTO currently have the same buckets, but that may change in the future. Below is a sample of the table setup.
Special PTO Buckets MinRangeValue MaxRangeValue
| <10 | 0 | 10 |
| 10-19 | 10 | 20 |
| 20-29 | 20 | 30 |
| 30-39 | 30 | 40 |
| 40 | 40 | 99999 |
I'm struggling to come up with a way to have a single measure determine which combination of buckets an employee is in. For example if an employee has 25 hours of 'special PTO' and 9 hours of 'regular PTO', I want them to show up on the matrix where '20-29' and '<10' intersect.
