bucketing
2 TopicsDynamic Segmentation of callers and calls
Hi there, I was hoping someone may be able to help with a dynamic segmentation query in Power BI. I have an activities table (SQLActivities) which contains columns called activityID and callerID. The principle of the table is that a single callerID can have multiple activityID. I'm trying to create two pie charts to show: 1) Callers grouped into buckets based on number of activities per caller, and 2) Activities grouped by the same buckets for each caller. For the second pie chart I am essentially trying to show how many activities in each caller bucket -i.e. if two callers had 11 or more activities, how many activities were actually in that bucket. I have created a table to specificy the values of the buckets - they are 1, 2, 3 - 5, 6 - 10, and 11 or more. I created the table as follows: ActivitySegments = Datatable( "Segment Key", INTEGER, "Bucket", String, "Min Activities", Integer, "Max Activities", Integer, { {1,"1",1,1}, {2,"2",2,2}, {3,"3 - 5",3,5}, {4,"6 - 10",6,10}, {5,"11 or more",11,99999999999} } ) for the first chart I've been able to create a measure to show the number of callers which fall into each bucket using the following dax: Activity Buckets - Callers = VAR Summary = SUMMARIZE ( FILTER(SQLActivities , SQLActivities [Filter Activities In Date] = 1), SQLActivities [c1_calleridid], "Bucket", SWITCH ( TRUE (), COUNTROWS ( SQLActivities ) = 1, "1", COUNTROWS ( SQLActivities ) = 2, "2", COUNTROWS ( SQLActivities ) <= 5, "3 - 5", COUNTROWS ( SQLActivities ) <= 10, "6 - 10", COUNTROWS ( SQLActivities ) > 10, "11 or more" ) ) RETURN SUMX ( Summary, IF ( [Bucket] = SELECTEDVALUE ( 'ActivitySegments'[Bucket] ), 1, 0 ) ) This seems to work well enough, but I cannot work out how to convert this to calculate the number of activities per caller bucket instead of the number of callers. The expected result would show the number of activities for callers per bucket.Solved552Views0likes1Comment
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!Solved1.4KViews0likes2Comments
