Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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:
Hi,
You may download my PBI file from here.
Hope this helps.
Thanks!!
You are welcome.
@Anonymous
could you pls also provide the expected output and the calculation logic?
Proud to be a Super User!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |