Forum Discussion

pbvisual's avatar
pbvisual
Regular Visitor
3 years ago
Solved

Dynamic Buckets based on Sum value that changes with date slicer

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 ...
binning
bucketing
date slicer
Dynamic
groupby
SUMMARIZE
  • johnt75's avatar
    johnt75
    3 years ago

    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
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

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's avatar
pbvisual
Regular Visitor
3 years ago

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!