## SSAS Distinct Count Sum

## SSAS Distinct Count Sum

(OP)

I have a cube that has invoices in it. Dimensions around the invoices are customers and item categories. Our marketing area wants to get a count of distinct item categories purchased by each customer. Then they want to take all the distinct counts at the customer level and sum them up and divide by the count of customers.

For example:

Customer A has purchased in 5 categories

Customer B has purchasedin 7 categories

The sum of the two customers distinct category count is 12.

Since there are 2 customers, the sum of 12 would be divided by 2 to get 6.

So the average item category per customer is 6.

My question is, how do I create this calculation?

I have the customer count and I have the item category count. The problem is that that item category count is a distinct count and instead of being summed up, it continues to be a distinct count, so the highest it ever gets is 19 (since there are 19 categories).

So what I need to do is figure out how to sum the distinct counts.

Thanks in advance!

For example:

Customer A has purchased in 5 categories

Customer B has purchasedin 7 categories

The sum of the two customers distinct category count is 12.

Since there are 2 customers, the sum of 12 would be divided by 2 to get 6.

So the average item category per customer is 6.

My question is, how do I create this calculation?

I have the customer count and I have the item category count. The problem is that that item category count is a distinct count and instead of being summed up, it continues to be a distinct count, so the highest it ever gets is 19 (since there are 19 categories).

So what I need to do is figure out how to sum the distinct counts.

Thanks in advance!