Dynamic Buckets based on Sum value that changes with date slicer

[pbvisual]

Hello Everyone,

I am trying to create a Matrix that dynamically shows count of Salesmen by Product under different buckets as I select different date ranges. The buckets are based on Sales Count as follows - No Sales, 1-10, 11-20, 21-30, and 31-40… (There is a table supporting below)

I followed multiple ways from the previous posts in the community, but couldn’t solve it. For all the methods I have tried, I get correct Salesmen count associated to the buckets as long as I have Product and Sales Person columns in the visual. When I remove the Sales Person column, it sums the Sales Count of all the Salesmen grouped by the Product and segments the Salesmen count under a wrong bucket.

Sample Data

Product	Sales Person	Sale Date	Sale Count
P1	A	2022-08-01	0
P1	A	2022-08-15	0
P1	A	2022-09-01	6
P1	A	2022-09-15	7
P1	A	2022-10-01	8
P1	A	2022-10-15	9
P1	B	2022-08-01	3
P1	B	2022-08-15	11
P1	B	2022-09-01	6
P1	B	2022-09-15	5
P1	B	2022-10-01	10
P1	B	2022-10-15	2
P2	C	2022-08-01	4
P2	C	2022-08-15	3
P2	C	2022-09-01	10
P2	C	2022-09-15	5
P2	C	2022-10-01	7
P2	C	2022-10-15	9
P2	D	2022-08-01	3
P2	D	2022-08-15	20
P2	D	2022-09-01	6
P2	D	2022-09-15	7
P2	D	2022-10-01	1
P2	D	2022-10-15	2

Sales Bucket	Min	Max
No Sales	0	0
1-10	1	10
11-20	11	20
21-30	21	30
31-40	31	40

Desired result

Aug		No Sales	1-10	11-20	21-30	31-40
	P1	1		1
	P2		1		1

Aug & Sep		No Sales	1-10	11-20	21-30	31-40
	P1			1	1
	P2				1	1

Aug & Sep & Oct		No Sales	1-10	11-20	21-30	31-40
	P1					2
	P2					2

Any help would be greatly apprecated. Thanks a lot in advance!

[johnt75 · Accepted solution]

You can use

VAR PeopleInSegment = FILTER(
	ALLSELECTED( 'Table'[Sales Person]),
	VAR SalesOfPerson = CALCULATE( SUM('Table'[Sale Count]))
	VAR SegmentForPerson = FILTER(
		'Buckets',
		NOT ISBLANK( SalesOfPerson) &&
			'Buckets'[Min] <= SalesOfPerson &&
			'Buckets'[Max] >= SalesOfPerson
	)
	VAR IsCustomerInSegment = NOT ISEMPTY( SegmentForPerson)
	RETURN IsCustomerInSegment
)
VAR Result = CALCULATE(
	COUNTROWS( VALUES( 'Table'[Sales Person])),
	KEEPFILTERS( PeopleInSegment)
)
RETURN Result

[pbvisual]

@johnt75, I applied it to the dataset and it is working as expected. I need to extend it to the real dataset and check the results. Thank you so much! Really appreciate your help!