How to segment customers by number of transactions by a new table

[RMSLUIS]

Hi,

I would like to know how to create this auxiliar table where I have a count of each unique customers displayed by how many times they completed a transaction. 

Many thanks in advance,

Rui

[zerotyper]

@v-zhangti  I'd like to borrow your file, thanks!

@RMSLUIS 

1st, create a table via the below code :

Segment = 
SELECTCOLUMNS(
   { 
       ( "High", 1  ) ,
       ( "Medium" , 2 ) ,
       ( "Low" ,3 )
   } ,
    "Segment" , [Value1] , "Orderby" , [Value2]
)

2nd, create a measure

# Customers = 
VAR _customerTbl =
    ADDCOLUMNS (
        VALUES ( 'Transaction'[Customer ID] ),
        "@Transaction",
            CALCULATE (
                DISTINCTCOUNT ( 'Transaction'[Transaction ID] )
            )
    )
VAR _Segment =
    MAX ( 'Segment'[Segment] )
RETURN
IF(
    HASONEVALUE( 'Segment'[Segment] ) ,
    SWITCH (
        TRUE (),
        _Segment = "High",
            COUNTROWS (
                FILTER (
                    _customerTbl,
                    [@Transaction] >= 4
                )
            ),
        _Segment = "Medium",
            COUNTROWS (
                FILTER (
                    _customerTbl,
                    [@Transaction] >= 2
                        && [@Transaction] < 4
                )
            ),
        COUNTROWS (
            FILTER (
                _customerTbl,
                [@Transaction] < 2
            )
        )
    ),
    COUNTROWS( _customerTbl )
)

3rd, put the column Segment[Segment] and measure into Matrix.

[v-zhangti]

Hi, @RMSLUIS 

You can try the following methods.

Table:

Table 2 =
SUMMARIZE (
    'Table',
    'Table'[Customer ID],
    "Transaction",
        CALCULATE (
            COUNT ( 'Table'[Customer ID] ),
            ALLEXCEPT ( 'Table', 'Table'[Customer ID] )
        )
)

Column:

Customer = CALCULATE(COUNT('Table 2'[Transaction]),ALLEXCEPT('Table 2','Table 2'[Transaction]))

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RMSLUIS]

Hi,

It works perfectly (by table).

If I want in the same table to add a new collum where I segment the customers by frequency. I mean like this....

Thank you very much!

Rui

[v-zhangti · Accepted solution]

Hi, @RMSLUIS 

Column:

Segment = SWITCH(True(),
[Transaction]>=4,"High",
[Transaction]<4&&[Transaction]>=2,"Medium",
[Transaction]<=1,"Low")

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.