# PowerPivot DAX -COUNTIF; Find customer will repeat orders

### Question

• I am looking for a DAX formula that return me the number of customers that has repeat orders.

 Invoice # Customer Item Quantity 1 A Bike 1 1 A Car 1 2 B Car 2 3 C Bike 2 3 C Car 1 4 D Bike 2 5 E Car 1 6 A Bike 2 7 C Car 1 8 D Car 1 9 A Bike 1 10 B Car 2 11 A Bike 2

In this case, the answer shall be

Item            # of Customer w/ R.O.

Bike                                 1

Car                                  2

• Edited by Tuesday, October 23, 2012 12:49 AM
Tuesday, October 23, 2012 12:47 AM

• Johnny -

Here's one way to address your original question.  Using SUMMARIZE to group by customer and item.  Then an IF to test if the number of distinct invoice #s is greater than 1.  Kind of similar to using SUM(CASE) in SQL for conditional counting.

```DstCntRepeatCustomer:=SUMX(
SUMMARIZE(Sales
,Sales[Customer],Sales[Item],
"DistinctCntRepeatCustomer",IF(DISTINCTCOUNT(Sales[Invoice #])> 1, 1, 0)
)
,[DistinctCntRepeatCustomer]
)```

Let me know if that works for your cases.  Not sure I follow your additional examples.  If this doesn't address those, please post more example data and expected outcomes, and I'm sure it can be tweaked to get the output you need.

HTH.
Brent

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

Monday, October 29, 2012 4:55 AM
• Try the below formula incase you are not able to use the Summarize function

`=countrows(filter(values(Table1[Customer Number]), calculate(countrows(values(Table1[SOP Number]))>1)))`

Cheers,
Jason

Tuesday, October 30, 2012 3:59 PM

### All replies

• Johnny,

Here's one way of doing this.

Step 1 - Create a calculated column to mark the repeated orders.

=IF(CALCULATE(COUNTROWS(Invoices);ALL(Invoices[InvoiceNr];Invoices[Quantity])) > 1;

"Yes";

BLANK()

)

Step 2 - Create a measure that counts distict customers where HasRepeatedOrders Flag = Yes

=CALCULATE(

DISTINCTCOUNT(Invoices[Customer]);

Invoices[HasRepeatedOrder] = "Yes"

)

Kind regards,

Frederik

Tuesday, October 23, 2012 11:22 AM

First of all, I had tried using your way but it gives me a syntax error on Step 1. My Powerpivot does not read ";", and then i replace ";" with "," but each row is blank because CALCULATE(COUNTROWS(Table1), ALL(Table1[InvoiceNr], Table1[Quantity]) gives each row a value of 1.

Secondly, I am actually doing sales analysis that has more columns which includes Salesperson, State, City, Year, Month... Therefore, I am looking for a measure that can do all the job (EX: return order of each item in State of California, return order of each item in year of 2012...).

Regards,

Johnny

Tuesday, October 23, 2012 6:06 PM
• Johnny -

Here's one way to address your original question.  Using SUMMARIZE to group by customer and item.  Then an IF to test if the number of distinct invoice #s is greater than 1.  Kind of similar to using SUM(CASE) in SQL for conditional counting.

```DstCntRepeatCustomer:=SUMX(
SUMMARIZE(Sales
,Sales[Customer],Sales[Item],
"DistinctCntRepeatCustomer",IF(DISTINCTCOUNT(Sales[Invoice #])> 1, 1, 0)
)
,[DistinctCntRepeatCustomer]
)```

Let me know if that works for your cases.  Not sure I follow your additional examples.  If this doesn't address those, please post more example data and expected outcomes, and I'm sure it can be tweaked to get the output you need.

HTH.
Brent

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

Monday, October 29, 2012 4:55 AM
•  Year Month SOP Number SOP Type Customer Number City State Item Quantity Unit Price Invoice Net Extended Price Sales Person 2012 1 i1 Invoice XAA San Francisco CA Bike 1 \$500 \$500 VOS 2012 2 r1 Return XAB LA CA Bike -1 \$500 -\$500 VOS 2012 2 i2 Invoice XAC San Francisco CA Car 5 \$1,000 \$5,000 VOS 2012 3 i3 Invoice XAD San Francisco CA Car 1 \$2,000 \$2,000 VTS 2012 3 i3 Invoice XAD LA CA Bike 10 \$450 \$4,500 VTS 2012 1 i4 Invoice XBB New York NY Bike 15 \$600 \$9,000 VBS 2012 1 i5 Invoice XCA New York NY Car 2 \$600 \$1,200 VBS 2012 1 i6 Invoice XCA New York NY Bike 3 \$600 \$1,800 VBS 2012 1 i7 Invoice XTC Houston TX Car 10 \$1,500 \$15,000 VOS 2012 1 i8 Invoice XAA San Francisco CA Bike 3 \$600 \$1,800 VOS 2012 1 i9 Invoice XBB New York NY Bike 5 \$600 \$3,000 VBS 2012 1 i10 Invoice XCA New York NY Car 1 \$5,000 \$5,000 VBS

Above is a sample of the data. I am looking outcomes of the pivot table like below:

1) Scenario A

Item             # of Customer with more than 1 Order

Bike                              2  (XAA, XBB)
Car                               1  (XCA)

2) Scenario B

Filter (="Bike")

Sales Person         # of Customer with more than 1 Order

VBS                                   1 (XBB)

VOS                                   1 (XAA)

The data is more 500,000 columns; and I am looking for a measure that does the job.

thank you!

Monday, October 29, 2012 10:19 PM
• Btw, how come my PowerPivot does not have SUMMARIZE function?
Monday, October 29, 2012 10:28 PM
• SUMMARIZE was introduced in PowerPivot2012.  Are you are still on v1 (2008R2 version)?  If so, can you upgrade to 2012?

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

Monday, October 29, 2012 10:38 PM
• Just tested it on the test data and works fine for both of the scenarios above.  As long as you are looking for repeat customers based on multiple invoices for a given customer / item combination.

Let me know if that produces the desired results on your actual data.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

Tuesday, October 30, 2012 3:12 PM
• Try the below formula incase you are not able to use the Summarize function

`=countrows(filter(values(Table1[Customer Number]), calculate(countrows(values(Table1[SOP Number]))>1)))`

Cheers,
Jason