Let us go through the problem statement:
How many customers did the transactions/orders of one, two etc.? based on the user selection (date range in our case) using the DAX measures.
One might think this is very easy in getting the result, before concluding let us go through the data structure.
If you observe the table (name: Fact Table) structure it just has three columns/fields, those are OrderDate, CustomerKey, PurchaseValue and it is not summarized. It would have been quick and easier when another column with number of orders against each customer with summarized data then you can take number of orders in X-axis and count of CustomerKey on Y-axis.
Dynamic segmentation using DAX measure:
Before jumping into dynamic segmentation, let us understand what the problem with the static segmentation is, in static segmentation(calculation) you cannot play with the user selection slicers like date slicer.
The dynamic approach is attained by DAX measures which gets calculated dynamic on fly (based on the user filter selection).
Count of orders measure is as below.
Order Count = COUNT(‘Fact Table'[CustomerKey])
Segmentation Table:
The important thing is to create a segmentation table. There are multiple ways to create the table. One way is to use the enter data option in Data group of Home ribbon. In this we deal with the what if parameter.
This will create a table called Segment with column Segment which has series of numbers from 1 to 10 and with inbuilt measure Segment Value, result as below.
Segment Value = SELECTEDVALUE(‘Segment'[Segment], 1)
Segmentation Measure
Let us have a fun with segmentation measure, it is quite interesting below.
Count of Customers =
var _CustomerOrders =
SUMMARIZE(
‘Fact Table’,
‘Fact Table'[CustomerKey],
“How Many Orders”,
[Order Count]
)
return
COUNTROWS(
FILTER(
_CustomerOrders,
[How Many Orders]=[Segment Value])
)
The measure name is Count of Customers, var _CustomerOrders is a virtual table which summarizes the number of orders for each customer. Finally, measure returns the count of rows of filtered table when number of orders matches segment value.
Building Visuals
We are ready to create visual and interpret the results. Choose the clustered column chart from visualizations, drop the segment column in X-axis well and Count of Customers measure in Y-axis well.
Now add a slicer from visualizations, drop the OrderDate column from the fact table to field well, below is the result,
Now just play with the date slicer and observe the count of customers value changes in each segment based on the date range.
Bonus:
Let us segment the customers according to the total purchase value, how many customers made purchase value in segment 1-1000, 1001-3000 etc.
The approach is same, we need a segmentation table. This time we create the table (name: Sales segmentation) using the Enter data option, below is the result.
Create measure to sum the PurchaseValue field as below.
Total Amount = SUM(‘Fact Table'[PurchaseValue])
Dynamic segmentation DAX query:
Count of Customers based on PurchaseValue =
CALCULATE (
DISTINCTCOUNT(‘Fact Table'[CustomerKey]),
FILTER (
VALUES ( ‘Fact Table'[CustomerKey] ),
[Total Amount] >= MIN ( ‘Sales Segmentation'[LowerLimit] )
&& [Total Amount] <= MAX ( ‘Sales Segmentation'[UpperLimit] )
)
)
We are calculating the distinct customers filtered by the total amount is in between segment range i.e. lower limit and upper limit.
Create the visuals as below and play with the date slicer and notice the behaviour of the column chart.
Srikanth Kotapatti
Data Analyst
Addend Analytics