maverick15
New Member
- Joined
- Jun 11, 2020
- Messages
- 5
- Office Version
- 365
- Platform
- Windows
Hello,
I'm trying to do some analysis based on customer purchase history. I want to be able to determine how many distinct customers bought 0, 1, 2, 3 or 4 different service products over a given time period (trailing four quarters to start with). Then I want to say that for customers that bought, for example, 3 services products, the total spend on non-services products was X. This way I want to see if customers that bought a more diverse set of services spend more on product, etc.
Data is coming from an Analysis Server via PowerPivot query. The data contains basic info - customer name, product, quarter and revenue. I can do the basic calculation of number of services by customer with a distinct count in a measure, but I can only then use that in the Values section of a pivot. I can't then use that as the Row value in the pivot to break out the different spending buckets, etc. I also want to be able to see all the various cuts of Year over Year revenue buckets. I want to be able to show something like:
To complicate things, if the sum of the customer's purchase of a particular product is negative, I don't want to include that when creating the bucket of how many services they purchased.
Thoughts?
Sample Data:
Customer Name Quarter Product Revenue
Customer 110 2019Q03 Product A 188757
Customer 110 2019Q03 Product B 96227
Customer 110 2019Q04 Product A 117491
Customer 110 2019Q04 Product B 482921
Customer 110 2020Q01 Product A 135100
Customer 110 2020Q01 Product B 113805
Customer 110 2020Q02 Product A 236588
Customer 110 2020Q02 Product B 178933
Customer 110 2020Q03 Product A 114719
Customer 110 2020Q03 Product B 45245
Customer 110 2020Q04 Product A 155390
Customer 110 2020Q04 Product B 91924
Customer 110 2021Q01 Product A 229211
Customer 110 2021Q01 Product B 283466
Customer 110 2021Q02 Product A 139803
Customer 110 2021Q02 Product B 691294
Customer 111 2019Q03 Product A 182433
Customer 111 2019Q03 Product C 146703
Customer 111 2019Q04 Product A 288057
Customer 111 2019Q04 Product C 155233
Customer 111 2020Q01 Product A 21966
Customer 111 2020Q02 Product A 330688
Customer 111 2020Q03 Product A 451153
Customer 111 2020Q04 Product A 44835
Customer 111 2021Q01 Product A 81862
Customer 111 2021Q02 Product A 270525
Customer 124 2019Q03 Product A 1250764
Customer 124 2019Q04 Product A 1078248
Customer 124 2020Q01 Product A 903909
Customer 124 2020Q02 Product A 1057728
Customer 124 2020Q03 Product A 1339983
Customer 124 2020Q04 Product A 1289459
Customer 124 2021Q01 Product A 1356515
Customer 124 2021Q02 Product A 509955
I'm trying to do some analysis based on customer purchase history. I want to be able to determine how many distinct customers bought 0, 1, 2, 3 or 4 different service products over a given time period (trailing four quarters to start with). Then I want to say that for customers that bought, for example, 3 services products, the total spend on non-services products was X. This way I want to see if customers that bought a more diverse set of services spend more on product, etc.
Data is coming from an Analysis Server via PowerPivot query. The data contains basic info - customer name, product, quarter and revenue. I can do the basic calculation of number of services by customer with a distinct count in a measure, but I can only then use that in the Values section of a pivot. I can't then use that as the Row value in the pivot to break out the different spending buckets, etc. I also want to be able to see all the various cuts of Year over Year revenue buckets. I want to be able to show something like:
To complicate things, if the sum of the customer's purchase of a particular product is negative, I don't want to include that when creating the bucket of how many services they purchased.
Thoughts?
Sample Data:
Customer Name Quarter Product Revenue
Customer 110 2019Q03 Product A 188757
Customer 110 2019Q03 Product B 96227
Customer 110 2019Q04 Product A 117491
Customer 110 2019Q04 Product B 482921
Customer 110 2020Q01 Product A 135100
Customer 110 2020Q01 Product B 113805
Customer 110 2020Q02 Product A 236588
Customer 110 2020Q02 Product B 178933
Customer 110 2020Q03 Product A 114719
Customer 110 2020Q03 Product B 45245
Customer 110 2020Q04 Product A 155390
Customer 110 2020Q04 Product B 91924
Customer 110 2021Q01 Product A 229211
Customer 110 2021Q01 Product B 283466
Customer 110 2021Q02 Product A 139803
Customer 110 2021Q02 Product B 691294
Customer 111 2019Q03 Product A 182433
Customer 111 2019Q03 Product C 146703
Customer 111 2019Q04 Product A 288057
Customer 111 2019Q04 Product C 155233
Customer 111 2020Q01 Product A 21966
Customer 111 2020Q02 Product A 330688
Customer 111 2020Q03 Product A 451153
Customer 111 2020Q04 Product A 44835
Customer 111 2021Q01 Product A 81862
Customer 111 2021Q02 Product A 270525
Customer 124 2019Q03 Product A 1250764
Customer 124 2019Q04 Product A 1078248
Customer 124 2020Q01 Product A 903909
Customer 124 2020Q02 Product A 1057728
Customer 124 2020Q03 Product A 1339983
Customer 124 2020Q04 Product A 1289459
Customer 124 2021Q01 Product A 1356515
Customer 124 2021Q02 Product A 509955
