Hi,
I wish to do a customer count by segmenting them according to their value over any financial year. My desired outcome would be identical to Figure 2 of this article except that I want financial year not calendar year. https://www.daxpatterns.com/dynamic-segmentation/
I have entered the segment definitions into a table called DimSegment (see attachment) and created a relationship between the MinAmt column of the DimSegment table and the AmtInclGst column of my sales table. I created a new measure to calculate how many customers fall into these value segments. The 2 issues encountered are:
1. The count of segmented customers is different from the discount count (implicit measure)
2. The customer count by segment isn't displayed. Could this be because my sales table has a row for each transaction and there could be multiple transactions by one customer. Do I first need to group the sales table by customer key, by financial year and sum the AmtInclGst?
Thank you.
Linda
Typo in item 1. This should read "distinct count" not "discount count"
Hi Linda,
It's not the SalesOfCustomer that's wrong. I suspect it's because in the COUNTROWS formula you've added the 'DISTINCT' function, whereas their formula doesn't have 'DISTINCT'. Their formula is:
Whereas your's is effectively:
Hope that helps.
Mynda
Hi Mynda,
Thanks but I implemented it that way to start with, and it didn't return any results either. Here is the formula I used in the original version. But the count of customers by segment is still blank. Any suggestions how to fix this would be great, thank you.
Hi Linda,
I suggest you download their sample file and inspect it and compare it to yours to identify differences in the way you have structured your model and written the measures. This is how I'd approach this problem if it were my file.
Mynda
