Forum Discussion

DimaMD's avatar
DimaMD
Solution Sage
4 years ago
Solved

Count Customers by segmentation

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

@dax @help
  • PaulOlding's avatar
    PaulOlding
    4 years ago

    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')
)

11 Replies

  • PaulOlding's avatar
    PaulOlding
    Solution Sage
    4 years ago

    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's avatar
      DimaMD
      Solution Sage
      4 years ago

      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's avatar
        PaulOlding
        Solution Sage
        4 years ago

        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