Count Of Orders By Customer ID

[TheGreatestGoat]

Hi Guys,

Tried a few solutions from the boards and none of them seem to be working for me. 

I have the following sales query, it's broken down in rows by product_id so there are many reoccurring values of order_name and customer_id :

I am trying to create a calculated column for the number of orders per customer. Currently I have a measure which works correctly and returns the right values:

Total Orders = DISTINCTCOUNT('Sales Fact Table'[order_id])

However I need this to be a column as I want to use 'order count' as a slicer. 

I have tried this:

Order Count =
CALCULATE (
    DISTINCTCOUNT ( 'Sales Fact Table'[OrderID] ),
    FILTER (
        ALL ( 'Sales Fact Table' ),
        [customer_id] = MAX ( 'Sales Fact Table'[customer_id] )
    )
)

 
This does return values, however they are incorrect.

Anyone know what the correct formula should be? 

Thanks! 

[Zubair_Muhammad]

@TheGreatestGoat

Try with following

Order Count =
CALCULATE (
    DISTINCTCOUNT ( 'Sales Fact Table'[OrderID] ),
    ALLEXCEPT ( 'Sales Fact Table', 'Sales Fact Table'[customer_id] )
)

[TheGreatestGoat]

Hey @Zubair_Muhammad,

Thanks so much for your reply, it helps a lot!

Tried this out but didn't work. Values were 10 or 20 times expected.

I also tried swapping around order_id and customer_id in your formula like this:

Customer Product Count = CALCULATE ( DISTINCTCOUNT ( 'Sales Fact Table'[customer_id] ), ALLEXCEPT ( 'Sales Fact Table', 'Sales Fact Table'[order_id] ) )

This was actually closer and returned the total number or products per customer. I.e returned the distinct values for product_id 

I.e. Customer A purchased 49 products. However it did not return the number of orders which was 14

This is a useful number to have as well, but not quite what I was after. I feel like the solution is close but not quite there. 

Any other ideas? 

[Zubair_Muhammad]

Hi

Could you copy paste some data with expected results.

If data is in a format (copiable) like following it becomes easy to find a solution

InvoiceDate	Product	Price	Expecetd Last Price
Sunday, October 1, 2017	Product A	15	4
Wednesday, November 1, 2017	Product B	10	5
Thursday, March 1, 2018	Product A	8	4
Friday, June 1, 2018	Product B	5	5
Friday, June 1, 2018	Product A	4	4

[TheGreatestGoat]

Hey @Zubair_Muhammad

Here is an exceprt of data for two customers. Thanks for your help 🙂

purchase_date	product_id	order_id	customer_id
24/05/2014	100	6970030474	6911468682
24/05/2014	200	6970029258	6911468682
24/05/2014	1111	6970028426	6911468682
24/05/2014	800	6970030090	6911468682
24/05/2014	100	6970029578	6911468682
24/05/2014	2000	6970028810	6911468682
27/05/2014	1100	6970098378	6911468682
1/06/2014	400	6970186122	6911468682
1/06/2014	900	6970186506	6911468682
1/06/2014	100	6970186954	6911468682
5/09/2013	3000	6966721034	6904791178
5/09/2013	2000	6966721034	6904791178
5/09/2013	1000	6966721034	6904791178
7/07/2014	900	6970956234	6904791178
8/07/2014	800	6970970506	6904791178
10/07/2014	1100	6970999754	6904791178
11/07/2014	400	6971025482	6904791178
29/01/2015	300	6980551690	6904791178
29/01/2015	100	6980551114	6904791178
29/01/2015	200	6980551690	6904791178
29/01/2015	200	6980551114	6904791178
29/01/2015	200	6980551114	6904791178
29/01/2015	50061	6980551114	6904791178
29/01/2015	1300	6980551690	6904791178
11/02/2015	20000	6980978890	6904791178
11/02/2015	6174	6980978890	6904791178
11/02/2015	8946	6980978890	6904791178

[Zubair_Muhammad · Accepted solution]

@TheGreatestGoat

When is Use the formula I proposed above I get the following results (which I expect)

Do you expect something else

[Anonymous]

Have similar scenario I am trying to figure out.  What if I wanted to take the count of orders where the Product ID was lets say less than 1000 in the example provided.  This would result in customer 6911458582 having 7 (instead of 10) in the example.

Appreciate any help in what additional logic should be applied.

[TheGreatestGoat]

Hi @Zubair_Muhammad

I'm sorry your solution does work indeed! 

I got confused, I had to turn it to 'Do Not Summarize Data' in my table!

Thanks so much for your help!