Customer Segmenting

[Anonymous]

Ok.  So I have a problem that should be easy to work through and still I am not able to do it.  I have been segmenting clients based on a variety of metrics to show how the GP % from that group has grown or declined over time.  The only way that I can't seem to segment is by total $ purchased in a givin year.  I am just looking to prove out my hypothisis which is that larger (over $500k per year) clients have declining GP % while smaller clients (under $500k) are more stable.  I pasted a sample dataset below.  I am sure that this has already been answered someplace so if someone can just point me in the right direction I would appretiate it.

Client	Signed	Budget	Cost
Client 1	6/7/2020	$1.08	$0.36
Client 1	9/3/2020	$1.11	$1.11
Client 1	9/3/2020	$3.32	$3.32
Client 1	9/3/2020	$5.09	$5.09
Client 1	12/5/2019	$6.32	$6.32
Client 1	7/22/2019	$7.20	$3.60
Client 1	8/30/2018	$10.46	$10.46
Client 1	9/3/2020	$20.08	$20.08
Client 1	6/30/2021	$21.50	$21.50
Client 1	9/3/2020	$22.03	$22.03
Client 1	9/3/2020	$33.39	$33.39
Client 1	9/3/2020	$35.86	$35.86
Client 1	9/3/2020	$44.04	$44.04
Client 1	9/3/2020	$58.09	$58.09
Client 1	6/30/2021	$64.13	$64.13
Client 1	6/30/2021	$115.72	$115.72
Client 1	9/3/2020	$122.63	$121.17
Client 1	9/3/2020	$125.54	$125.54
Client 1	6/30/2021	$128.27	$128.27
Client 1	9/3/2020	$137.86	$137.86
Client 2	9/3/2020	$146.71	$143.79
Client 2	6/30/2021	$148.05	$148.05
Client 2	9/3/2020	$170.52	$170.52
Client 2	7/22/2019	$172.23	$57.41
Client 2	9/3/2020	$185.96	$185.96
Client 2	6/30/2021	$189.49	$189.49
Client 2	5/8/2019	$206.22	$206.22
Client 2	7/2/2019	$209.01	$69.67
Client 2	6/30/2021	$229.28	$229.28
Client 2	12/5/2019	$233.99	$233.99
Client 2	7/2/2020	$241.49	$118
Client 2	6/30/2021	$251.95	$251.95
Client 2	7/2/2019	$264.81	$88.27
Client 2	6/30/2021	$267	$89
Client 2	9/3/2020	$267.25	$236.43
Client 2	8/30/2018	$273.43	$273.43
Client 2	8/30/2018	$294.82	$294.82
Client 2	8/30/2018	$296.74	$296.74
Client 2	8/30/2018	$298.41	$298.41
Client 2	8/30/2018	$299.48	$299.48
Client 2	1/27/2020	$303.03	$303.03
Client 2	7/2/2020	$303.24	$100.98
Client 2	8/30/2018	$303.37	$303.37
Client 2	7/2/2019	$304.56	$101.52
Client 3	7/2/2020	$304.71	$101.47
Client 3	8/30/2018	$307.09	$307.09
Client 3	8/30/2018	$307.96	$307.96
Client 3	8/30/2018	$308.01	$308.01
Client 3	7/2/2020	$318.56	$106.08
Client 3	7/2/2020	$319.13	$106.27
Client 3	7/2/2020	$320.45	$106.71
Client 3	7/2/2020	$325.20	$108.40
Client 3	7/2/2019	$329.01	$109.67
Client 3	7/2/2020	$330	$107.82
Client 3	7/2/2020	$330	$108.01
Client 3	7/2/2020	$330	$109.01
Client 3	7/2/2020	$332.37	$110.79
Client 3	6/30/2021	$334.38	$111.46
Client 3	7/2/2020	$334.65	$111.55
Client 3	9/3/2020	$336.56	$341.29
Client 3	7/2/2020	$339.66	$111.54
Client 3	7/2/2020	$340	$113.22
Client 3	7/22/2019	$342.59	$113.83
Client 3	7/22/2019	$342.59	$113.92
Client 3	7/22/2019	$342.59	$114.19
Client 3	7/2/2020	$343.38	$114.46
Client 3	7/2/2020	$346.92	$115.64
Client 3	7/2/2019	$348.96	$116.32
Client 3	7/2/2019	$349.32	$116.44
Client 3	9/3/2020	$353.71	$353.71
Client 3	7/2/2020	$358.83	$119.61
Client 3	7/2/2020	$359.94	$119.98
Client 3	9/3/2020	$360.42	$360.42
Client 3	6/30/2021	$365.62	$121.87
Client 3	7/2/2020	$377.12	$123.43
Client 3	7/2/2020	$377.13	$125.05

[Ashish_Mathur]

Hi,

How do you propose to visualise this.  So in a simple matrix visual, on the row labels there will be 2 rows - <=500K and >=500K.  What do you want to see in the column labels of the matrix visual?  Please show the expected result clearly.

[Anonymous]

I am thinking matrix but I would also like to be able to use the segments as a slicer.  The matrix would look like this:  

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thanks!!

[Ashish_Mathur]

You are welcome.

[ryan_mayu]

@Anonymous 

could you pls also provide the expected output and the calculation logic?