none
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

    Please help! thank you!


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

Answers

  • 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
    // Please mark correct answers and helpful posts //
    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
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter


    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
  • Thank you for your reply, Frederik!

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

    Please advise. Thank you very much!!

    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
    // Please mark correct answers and helpful posts //
    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
    // Please mark correct answers and helpful posts //
    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
    // Please mark correct answers and helpful posts //
    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
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter


    Tuesday, October 30, 2012 3:59 PM
  • Thank you so much Brent!

    I have upgraded to PP v2 and it works on data!

    Tuesday, October 30, 2012 5:40 PM
  • thank you Jason for your alternative answer!
    Tuesday, October 30, 2012 5:40 PM