Forum Discussion
Churn Rate KPI and Defining Client Type using DAX
You may use DAX below to add calculated column and measure.
Column =
IF (
RANKX (
FILTER ( Table1, Table1[Client] = EARLIER ( Table1[Client] ) ),
Table1[Year],
,
ASC,
DENSE
)
= 1,
"New",
"Repeat"
)
Measure =
VAR y =
SELECTEDVALUE ( Table1[Year] )
VAR t =
CALCULATETABLE ( VALUES ( Table1[Client] ), Table1[Year] = y - 1 )
RETURN
DIVIDE (
COUNTROWS ( EXCEPT ( t, VALUES ( Table1[Client] ) ) ),
COUNTROWS ( t )
)
Thanks so much for the quick response. I will give this a go as soon as I find time.
Rgds,
N.
Your solution does work. However, the data that I will be receiving from our accounting system will now be received in a different form, and will require quite a lot of versitility and pivoting by location. As such, the static form of the calculated column "new/repeat" solution will not work. This is my fault as my initial query on this thread has now changed. I hope it won't be too much trouble for me to update this thread with the revised version of the query I have... my apologies!
I will send this over next week. Many thanks again,
Rgds,
N.
Apologies for the delay in writing back. The accounting software that houses our fee information will now be able to populate PowerBI automatically. This means it will be in a format different to the table style I mentioned above and, as such, isn't as helpful for Calculated Column purposes - or at least my understanding of them...
A table will be input in this format:
Date Year Client Name Sales Dept. Client Location Sales Type
6/2/18 2018 Client A Thailand UK Product A
This table will house tens of thousands of individual sales records over the course of about 10 years (it is at annual level that I will need to distingish between New and Repeat clients), and will use this to compare and track a "Churn Rate" KPI, and also track weightings using visualisations.
However, the report I am designing will need to be flexible and pivot in a way where I am able to provide New/Repeat/Churn Rate analysis on a year-by-year basis at macro level (i.e. organisation as a whole), and also at micro-level, piviting the data by Client Location, Sales Dept etc.. It is this latter part that did not work on your original solution to which I am replying.
I appreciate my initial description of what I needed was not helpful; my apologies for this.
Please let me know if there is any way of using Measures instead of CC to resolve this.
Again, thanks very much in advance for your help.
Rgds,
Nick.
There is something missing where are the Sales (Order) table position in this calculation?
e.g: We add a new customer to the customers' table but what if he didn't purchase any product before?
So I think we need to consider the purchases of the products not only the customer when he joined the community.
