Count Customers by segmentation

[DimaMD]

We have a table of sales, which consists of following collums:

1) Buying date

2) Order Number (ID)

3) Customer key (ID)

From this table we make a mesure, that segments our customers  by several rules. 

By using this mesure we can see what segment appears to every customer in month period by amount of his purchases.

The goal is to dynamicly see amount of customers, that belong to specific segment in several months perion. 

For example

In august we have 200 cliens in Newcomer segment, 300 clients in buyers segment etc. In september those same customers can be identified as other segment, it depends on rules which they complete in our mesure, so we can see other numbers, for example 150 newcomers, 350 buyers.

By having this numbers of clients in different segments and months we need to build bar chart or graph, that will show us dynamic change in amount of customers in segments.

At the moment we only have matrix part, that shows us change of segments by customers, but we can't count quantity of customers that belongs to each segment.

File example is in attachment

[PaulOlding]

Hi @DimaMD 

To display the segments in a bar chart / visual we'll need the values stored in a column.  I used the Enter Data button to add the segment names in a disconnected table

Next I created a measure to count customers per segment

Customers per Segment Count = 
VAR _CustomerSegments =
ADDCOLUMNS(
	VALUES(Sales[CustomerKey]),
	"Segment", [Segmentation purchase]
	)
VAR _SegmentCustomerCount =
GROUPBY(
	_CustomerSegments,
	[Segment],
	"# Customers", COUNTX ( CURRENTGROUP (), 1 )
	)
VAR _Result = 
FILTER(
    _SegmentCustomerCount,
    [Segment] = SELECTEDVALUE(Segments[Segment])
)
RETURN
    MAXX(_Result, [# Customers])

It uses the GROUPBY function to allow us to do an aggregation by a column with no lineage (ie the Segment column we add in _CustomerSegments).

The part that uses SELECTEDVALUE will ensure you have only one Segment in the current filter context to get a result

You might want to combine the _SegmentCustomerCount and _Result steps into 1.  I had them separate to debug / validate as I went along.

The final result:

[DimaMD]

Hi, @PaulOlding 
Thank You, but in diagrahm we can see 3 segments, while table has 5. I can not understand how to show all 5 segments in diagrahm

[PaulOlding]

There's a 'show items with no data' option you can select which will make the chart show segments with Blank result.

I guess an alternate would be to make the measure return 0 rather than blank by changing the return part

MAXX(_Result, [# Customers]) + 0

[DimaMD]

On screenshots 1 and 2 we can see that in table we have several customers with those segments, that are shown as "0" in counting table. So as a result, we have customers that are shown in table in 5 different segments, but counting table does not count them for some reason.  The question is how to count them as well?
 
12

Maybe the problem is that we have a condition, which must show customers that made their last purchase more than 30 days ago, but we seperate results by months, so I think maybe mesure doesnt count customers that made last purchase in previous months while we want to see result for current month.

The example of mesure, how we segment customers you can see below

Segmentation purchase = 
VAR data1 = VALUE( MAX('Date'[Date]) - [FirstPurchase]) -- counts how many days ago customer made his first purchase
VAR data2 = VALUE( MAX('Date'[Date]) - [LastPurchase]) -- counts how many days ago customer made his last purchase
VAR vFrequency =
SWITCH(
    TRUE(),
    AND([CountPurchase] = 1,data1< 14), "Newcomer",
    AND([CountPurchase] = 1,data2 > 30), "One-time buyer",
    AND([CountPurchase] >=2, data2 < 30), "Buyers",
    AND([CountPurchase] >=4, data2 < 30), "Constant",
    AND([CountPurchase] >= 2, data2 > 30), "Lost", 
    "Others"
    )
RETURN
 vFrequency

[PaulOlding · Accepted solution]

The difference in your screenshots is because the first table is not filtering by month, but the second one is.  This causes the result of [Segmentation purchase] measure to be different.

I think you're on the right track with the 'purchase > 30 days' comments.  Right now [First Purchase] and [Last Purchase] are the first and last in the current month. 

Should they be the first for all time and last in the current month?

FirstPurchaseAllTime = 
CALCULATE(
    MIN('Sales'[Order Date]) ,
    REMOVEFILTERS('Date')
)

[DimaMD]

@PaulOlding ,

---

---

Should they be the first for all time and last in the current month?

 Yes, the results for first purchase must be for all time, the last purchase should be the last purchase date till current analyzing month included (so previous months are also included).

[PaulOlding]

@DimaMD 

I'm wondering if [CountPurchase] should be sales from the beginning of time to the current month, rather than sales in the current month only (as it is now).

So,

Let's say we have a customer who buys once in month 1 then once again in month 2.

For month 1 they're a one-time buyer or Newcomer, depending on if the sale was in the 1st or 2nd half of the month

For month 2 they're a Buyer....  Their all time sales up to that point will be 2 and the most recent one in the last 30 days.

I'm not sure what you're aiming at with the segments.  Perhaps it would be useful to describe them in words, then we can try to get the code right>

[DimaMD]

Hi,@PaulOlding ,

What measure can show us the number of days from the last purchase to the maximum date in the slicer calendar,

I have incorrect data in the matrix,

And LasrPuchhase's measure is wrong

[PaulOlding]

Does this get you the result you want?

Days Since Last Purchase = 
VAR _CurrentDate = MAX('Date'[Date])
VAR _LastPurchase = 
CALCULATE(
    MAX(Sales[Order Date]),
    'Date'[Date] < _CurrentDate
)
VAR _Result = INT(_CurrentDate - _LastPurchase)
RETURN 
    _Result

[DimaMD]

@PaulOlding 

Yes, thanks

 it should help me

[DimaMD]

@PaulOlding 
We came to result. Your posts gave my thoughts the right track. Thank you very much for help!