Count the # of Customer Repeat Order

[ADSL]

Hi BI Community Team,

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

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?

Thanks and Regards,

[Greg_Deckler]

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

[ADSL]

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,

[Greg_Deckler]

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

[ADSL]

Hi @Greg_Deckler ,

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

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 

[Greg_Deckler]

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

[ADSL]

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.

[Greg_Deckler · 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
    )

[ADSL]

Hi @Greg_Deckler,

I greatly appreciate your help!

Best Regards,