Grouping / Binning with Maximum and other filters

[jjoedadorr]

Hi, Everyone

I'm currently in the process of creating a chart that tallies the number of employees based on their total training hours. I aim to organize these counts into bins/groups with a size of 0.5 hours. The column chart should adhere to the following criteria and functionalities:

• binning should only include the count of employees with more than 0 training hours.
• The binning system should include a filter that allows for specifying both a minimum and maximum value. Notably, the maximum value should include counts of employees surpassing this threshold.
• The chart should remain responsive when filtered by date.

I have two datasets, namely "employeeDetails" and "trainingHours," with a relationship via the 'Employee ID' column. 

employeeDetails

Employee ID	Employee Name
111	Ben
112	Daniel
113	David
114	James
115	John
116	Samuel
117	William
118	Emma
119	Thomas
120	Mia
121	Elizabeth
122	Alex
123	Lucas
124	Eva
125	Charlie

trainingHours:

Employee ID	Training Hours	Date
111	1.19	1/4/2023
111	1.10	1/5/2023
112	1.10	2/1/2023
112	0.40	2/1/2023
112	0.30	2/4/2023
113	3.50	1/2/2023
115	1.10	1/5/2023
115	0.20	2/3/2023
116	2.00	1/18/2023
116	1.00	2/2/2023
116	1.10	2/6/2023
117	3.00	1/6/2023
118	0.20	1/8/2023
120	0.21	1/4/2023
120	0.17	2/1/2023
121	0.12	1/22/2023
121	0.23	2/4/2023
121	0.44	2/19/2023
121	0.50	2/26/2023
122	0.62	2/1/2023
123	0.00	1/23/2023
124	1.10	1/19/2023
125	3.50	1/16/2023

Upon performing the necessary calculations, the grouping/binning chart structure should resemble the following:

minimum set to: 0.10

maximum set to: 3.00

note: maximum is set to 3.00 and employees with training hours greater than it should also be included (to the count) in this bin

and here's the same in table format:

Group/Bin	Count
0.10	2
0.50	1
1.00	3
1.50	1
2.00	1
2.50	0
3.00	4

Overall, the calculation I performed in Excel looks like the below:

Thank you in advance for any assistance you can offer in achieving this data visualization. Your expertise is greatly appreciated

[Ashish_Mathur]

Hi,

I have a similar problem in the attached PBI file.  Study the method/formulas in this file and apply them to yours.

[jjoedadorr]

Hello and thank you for sharing the PBI file. This demonstrates a useful approach for segmenting the figures and it's evident that the table is well-structured. However, I've noticed that when a 'categorical' filter is applied, it sets a static value. This means that, although your example indicates the max of '>$1M' (3.00 in my case), the flexibility to adjust it to, let's say, >500k doesn't seem to effectively group sales surpassing that new threshold. But thanks for sharing your insights. Your expertise in addressing this challenge is greatly appreciated, and it's valuable information that will help me refine my approach to this.

[v-shex-msft]

Hi @jjoedadorr ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @jjoedadorr,

It seems like a segmentation analysis requirement, I'd like to suggest you take a look at the following links if these help:

Static segmentation – DAX Patterns

Dynamic segmentation – DAX Patterns

Regards,

Xiaoxin Sheng