Distinct Count of Customers By Product as column value?

maverick15

New Member
Joined
Jun 11, 2020
Messages
5
Office Version
  1. 365
Platform
  1. 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:

sample-output.png


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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Watch MrExcel Video

Forum statistics

Threads
1,126,896
Messages
5,621,495
Members
415,844
Latest member
Reda Fouad Ramzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top