Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pbvisual
Regular Visitor

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 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

 

ProductSales PersonSale DateSale Count
P1A2022-08-01               0
P1A2022-08-15               0
P1A2022-09-01               6
P1A2022-09-15               7
P1A2022-10-01               8
P1A2022-10-15               9
P1B2022-08-01               3
P1B2022-08-15             11
P1B2022-09-01             6
P1B2022-09-15             5
P1B2022-10-01             10
P1B2022-10-15             2
P2C2022-08-01             4
P2C2022-08-15             3
P2C2022-09-01             10
P2C2022-09-15             5
P2C2022-10-01             7
P2C2022-10-15             9
P2D2022-08-01             3
P2D2022-08-15             20
P2D2022-09-01             6
P2D2022-09-15             7
P2D2022-10-01             1
P2D2022-10-15             2

 

Sales BucketMinMax
No Sales00
1-10110
11-201120
21-302130
31-403140

 

Desired result

 

Aug No Sales1-1011-2021-3031-40
 P11 1  
 P2 1 1 

 

Aug & Sep No Sales1-1011-2021-3031-40
 P1  11 
 P2   11

 

Aug & Sep & Oct No Sales1-1011-2021-3031-40
 P1    2
 P2    2

 

 

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

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

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, 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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors