Grouping Data into Two Different Buckets

Anonymous — Wed, 25 Mar 2020 20:57:08 GMT

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.

Re: Grouping Data into Two Different Buckets

Cristian_Angyal — Thu, 26 Mar 2020 07:20:28 GMT

Hi Anonymous ,

There is a recent series of articles by Reza Rad on www.radacad.com that might help you.

Check out this blogpost: https://radacad.com/age-banding-in-power-bi-using-treatas-dax-function-relationship-based-on-between

If you liked my solution please give it a thumbs up 👍.

If I did answer your question please mark my post as a solution ✅.

Thank you !

Cristian

topic Grouping Data into Two Different Buckets in DAX Commands and Tips

Grouping Data into Two Different Buckets

Re: Grouping Data into Two Different Buckets