Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Grouping Data into Two Different Buckets

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

<10010
10-191020
20-292030
30-393040
404099999
 
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.