2

I'm trying to create a calculated column in powerpivot to count the distinct orders per customer. The table (Sales) contains product information so has multiple rows per order and multiple orders per customer.

I want this to be a calculated column as I will use the results to build a frequency/histogram table.

I have been trying the following formula but it is very slow and it not bringing any results back.

=CALCUALTE(DISTINCTCOUNT(Sales[OrderNumber]),FILTER(Sales,Sales[Email]=EARLIER(Sales[Email])))

Does anyone have another idea how I could structure the formula to be faster? The table contains 13 million records.

Thanks

Mike

Mike B
  • 21
  • 1
  • Why are you applying the filter using the Email? Note generally string comparision is heavier than comparing numbers, is it the only way you have to filter the customer? Specify the structure of your table in order to help you. – alejandro zuleta Dec 01 '16 at 12:50
  • Mike, presumably you have multiple orders per customer but for a histogram you need one record per email. How many customers are we talking? – Jacob Dec 01 '16 at 18:43

0 Answers0