Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

group customer by orders

Hi,  My tasks if to calculate total customers into groups based on the total orders they made.  For example:  Total customers who made from 1 to 5 orders.  Total customers who made from 6 to 10 o...
Need Help
Show and Tell
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    Hi again Anonymous 

    I updated my PBIX with the same dataset you have used in your Tableau workbook.

    My existing measure is actually sufficient to do what you want.

     

    I created the same visual you had in Tableau:

    1. Created First Purchase Date calculated column in Orders and related this to a First Purchase Date table:
      Date of First Purchase = 
CALCULATE ( 
    MIN ( Orders[Order Date] ),
    ALLEXCEPT ( Orders, Orders[Customer ID] )
)
      Note that I hid this column in Report View
    2. Created Monts Since First Purchase calculated columns:
      Months Since First Purchase = 
VAR MonthIndex =
    DATEDIFF ( Orders[Date of First Purchase], Orders[Order Date], MONTH )
RETURN
    "M" & FORMAT ( MonthIndex, "00" )
    3. Applied a Page Level Filter, Segment Name = "1 to 5"
    4. Placed my earlier measure Number of Customers by Order Count Segment in a matrix visual, with some conditional formatting similar to your Tableau example.

    You could also create measures that apply particular segment filters, such as

    Number Customer Purchase 1 to 5 =
CALCULATE ( 
    [Number of Customers by Order Count Segment],
    Segment[Segment Name] = "1 to 5"
)

    PBIX attached. Please post back if needed.

     

    Regards,

    Owen

     

    Note that when including both Months Since First Purchase and First Purchase Month on the visual , all customers have five or fewer orders.

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
7 years ago

Hi Anonymous 

In this situation, with Power BI you would typically use a Dynamic Segmentation pattern
(see here).

 

I have attached a sample PBIX using a version of the Superstore data.

 

The steps are:

  1. Load the Orders table
  2. Create a Segment table that looks like:

     

  3. Create a measure Number of Customers by Order Count Segment
    following the pattern from the above link (more-or-less):
    Number of Customers by Order Count Segement = 
IF (
    ISFILTERED ( Segment[Segment Name] ),
    COUNTROWS (
        FILTER (
            VALUES ( Orders[Customer ID] ),
            VAR OrderCount = CALCULATE ( DISTINCTCOUNT ( Orders[Order ID] ) )
            RETURN
            COUNTROWS (
                FILTER (
                    Segment,
                    ( OrderCount >= Segment[Min Value] || ISBLANK ( Segment[Min Value] ) )
                    && ( OrderCount <= Segment[Max Value] || ISBLANK ( Segment[Max Value] ) )
                )
            ) > 0
        )
    ),
    DISTINCTCOUNT ( Orders[Customer ID] )
)
  4. Create a visual grouped by Segment Name with this measure:

    This measure will naturally respond to any other filters applied.

Regards,

Owen

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Dear, 

Thank you for your solution. 

Could you please to spend time to make the customer segmentation into separated group? 

Like I did in Tableau, I calculated total number of different group of customer who made orders from 1-5, 6-10, 10+ 

In this case, you dont need to separate into B2B or C2C. just number of customer from each orders segmentation.

I would use this to build cohort analysis like this:

 

Thanks for help!!