cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ADSL
Helper V
Helper V

Count the # of Customer Repeat Order

Hi BI Community Team,

 

I am counting the number of customer repeat order in excel file and using pivot tablet as the screenshot below.

 

2022-09-30_21-57-13.jpg

 

Source: https://drive.google.com/drive/folders/1EK-1SRY-P3R8AxDQhaqH84qebqnS9kdn?usp=sharing 

 

So any suggestion the measure calculation in Power BI to meet the requirement format below?

 

2022-09-30_21-56-34.jpg

Thanks and Regards,

1 ACCEPTED SOLUTION

@ADSL OK, PBIX attached below sig. I implemented this with calculated columns but could be done with measures, etc.

Count = 
    VAR __Customer = [Customer Code]
RETURN
    COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Order',[Customer Code] = __Customer),"__InvoiceNo",[InvoiceNo])))


Category = 
    SWITCH(TRUE(),
        [Count] = 1, "1",
        [Count] < 4, "2 - 3",
        [Count] < 7, "4 - 6",
        ">7"
    )



Category Sort = 
    SWITCH(TRUE(),
        [Count] = 1,1,
        [Count] < 4,2,
        [Count] < 7,3,
        4
    )

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

@ADSL So, are you importing that pivot table into Power BI then? Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler,

 

Sorry to make you are confusing of this topic.

 

Pivot table that I have mentions, it's refer to doing manual and count not mean importing.

 

Purpose to ask your support/suggestion if we're doing in Power BI, how to create the measure and calculate it?

 

Thanks and Regards,

@ADSL So what does your source data look like in Power BI?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,

 

Currently I have been created a measure of Total Customer as screenshot below.

 

2022-10-01_23-02-15.jpg

For Repeat # of Time Order measure, I don't know how to create it.

 

You can check the data source in this link - https://drive.google.com/file/d/1Fn9v62evTCEc5y93E5Hw9yGStDgZWrA8/view?usp=sharing 

@ADSL Check out the PBIX here for New and Returning Customers: New and Returning Customers - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,

 

Thank you for your feedback and link.

 

But It seem not meet the topic purpose that actually we want to know the customer's repeat time order.

 

Let's say how many times of customer A take order from us in this month and we simplify the time of order to: 1 time, 2 times - 3 times,...etc.

@ADSL OK, PBIX attached below sig. I implemented this with calculated columns but could be done with measures, etc.

Count = 
    VAR __Customer = [Customer Code]
RETURN
    COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Order',[Customer Code] = __Customer),"__InvoiceNo",[InvoiceNo])))


Category = 
    SWITCH(TRUE(),
        [Count] = 1, "1",
        [Count] < 4, "2 - 3",
        [Count] < 7, "4 - 6",
        ">7"
    )



Category Sort = 
    SWITCH(TRUE(),
        [Count] = 1,1,
        [Count] < 4,2,
        [Count] < 7,3,
        4
    )

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler,

 

I greatly appreciate your help!

 

Best Regards,

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.