# Distinct Count of Customers By Product as column value?

#### maverick15

##### New Member
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

### 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.

Replies
1
Views
98
Replies
0
Views
158
Replies
0
Views
469

1,126,896
Messages
5,621,495
Members
415,844
Latest member

### 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.

### Which adblocker are you using?

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

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