Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JeroniJamboni
Frequent Visitor

Count number of orders per customer in column

Hi Guys,

 

I'm relatively new with dax formula's and trying to work my way through the data I have to become a real DAX-Master;). Bought the book and visit this website regularly, which is very helpfull! At the moment however i seem to have a lot of difficulties trying with finding returning customers. I know that there are a lot of posts out there, which propably know already, but i can't seem to find a solution to my question.

 

I have a calculated sales table with:
Orderdate, OrderID, ProductID, CustomerID, Orderstatus
(by the way: Multiple order lines per order ID)

I would like to know how i could get a calculated column which calculates the number of previous orders of this customers. I would really like to have it filtered to order state. Only orders with the "completed"-status should be counted. 

Hope my question is clear. I tried almost everything. I can't find a good solution. I hope you guys can be of any help.

Jeroen

1 ACCEPTED SOLUTION

Hi,

 

Thanks for you answer. Works great to find the total number of orders, but i wanted to get the sequential number of the order. 

I found this solution, which works great!

Order sequence = 

COUNTROWS(
    FILTER(
        CALCULATETABLE(
            TableOrder;
            ALLEXCEPT(TableOrder;TableOrder[CustomerID])
        );
        TableOrder[Orderdate] < EARLIER(TableOrder[Orderdate])
        &&
        TableOrder[Orderstate] = "Complete"
    ))+1

By the way. The +1 is used to eliminate the zero's which it returns for first-time orders;).

Thanks anyway for your help. Got me thinking:D

Jeroni

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

HI @JeroniJamboni,


I think it will be help if you provide some sample data to analysis.


Below is the sample measure which I haven't test on real data, maybe you can try it if it suitable for your requirement:(create table visual with 'customer id' and below measure)

Order Count =
CALCULATE (
    DISTINCTCOUNT ( Table[OrderID] ),
    FILTER (
        ALL ( Table ),
        [CustomerID] = MAX ( Table[CustomerID] )
            && [Orderstatus] = "completed"
    )
)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi,

 

Thanks for you answer. Works great to find the total number of orders, but i wanted to get the sequential number of the order. 

I found this solution, which works great!

Order sequence = 

COUNTROWS(
    FILTER(
        CALCULATETABLE(
            TableOrder;
            ALLEXCEPT(TableOrder;TableOrder[CustomerID])
        );
        TableOrder[Orderdate] < EARLIER(TableOrder[Orderdate])
        &&
        TableOrder[Orderstate] = "Complete"
    ))+1

By the way. The +1 is used to eliminate the zero's which it returns for first-time orders;).

Thanks anyway for your help. Got me thinking:D

Jeroni

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.