Forum Discussion
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
| 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!
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
2 Replies
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
