Forum Discussion

srl01's avatar
srl01
Helper II
8 years ago
Solved

Customer segmentation over time

I'm working with a sales transaction dataset with unique customer IDs for each customer, and want to graph customer segments overtime (defined various ways, but to start off I want to split into 2 se...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi srl01,

     

    You can try to summarize the data first. Then the other steps can be based on the summarized table.

    1. Create a date table if there isn't one.

    Datetable =
CALENDAR ( DATE ( 2010, 1, 1 ), DATE ( 2017, 12, 31 ) )

    2. Establish a relationship between Date table and SalesRawData table.

    3. Create a summary table.

    SummaryTable =
SUMMARIZE (
    SalesRawData,
    'SalesRawData'[customerCode],
    'Date'[Date].[Year],
    'Date'[Date].[Month],
    "TransactionsLast12Months", CALCULATE (
        DISTINCTCOUNT ( SalesRawData[TransactionCode] ),
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -1, YEAR )
    )
)

    4. Add a new column to the summary table. 

    CustomerSegment =
IF ( [TransactionsLast12Months] > 6, "FrequentVisitors", "Others" )

    5. Create a visual.

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

     

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

So, you will need to employ the disconnected table trick. So, create a separate table that solely consists of the possible values of your measure. 

 

Then you will need a measure for each possible value, in your case it may be the same (the one from your post). Then you need a final measure like this:

 

Measures to Show = 
IF(
     HASONEVALUE(Employees[Employee]),
     SWITCH(
          VALUES(Attendance[Attendance]),
          "Attended",[Attended],
          "Not Attended",[NotAttended]
	 ),
	 MAX([Date])
)

In this example, Employees[Employee] is a column in the visual. The Attendance[Attendance] table/column is the disconnected table and there are two rows in it, "Attended" and "Not Attended". [Attended] and [NotAttended] are my two measures.