Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Everyone!!
I created some sort of a Cluster usind the RFM Model (Recency, Frequency, Monetary) directly in a calculed collumn, so with that I can have the name of the cluster for each one of my clients. (Champions, Loyal Costumers, and others).
But the problem is that I don't have any record of that. Everyday the clients can change their cluster but their previous cluster does not became a record so I can see the evolution throught time.
With this model I can assign different actions to different clusters but if I can't keep on tracking the increase or decrease (just an example) of the Champions or Loyal Costumers, I will only be doing actions without knowing if it is working or not.
Does anyone have a suggestion? As I mentioned, I created all the parameters and using IF conditional I created the clusters in a calculed column.
Thank you very much in advance!
Hi @Anonymous ,
How is the granularity of the information are you getting data at a daily basis, or every refresh overlays the data?
If you have daily information for the calculation you will need to make a measure to calculate the values within each Cluster based on the daily values, if you are getting information that is ovverwritten every single day, you need to have a way of getting the daily information.
Can you share some setup and mockup data?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
Do you want to calculate this based on the update date of the information or on the order date?
I have prepared a setup based on a date dimension table and the difference to the selected value of the orders.
So basically just replace your TODAY() from your days without orders to a MAXIMUM date, and all the formulas are with measures.
I will send the explanation and file between today and tomorrow.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI want to calculate based on the update date, for example: How many of each clusters I had on December 1st, how many on December 2nd, throught every day. Also would be nice to see how many of each clusters through the months:
How many cluster when I was in 31 October, 30 November, and so on...
Did you get it?
Thank you very much!!
Hi @Anonymous ,
You don't have an updated date on your file. So you are not saving daily information, you refer that you overwritte the data so without that date this is not possible to do.
Regarding month by month I have everythi prepared.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
I created the following measures:
Last Order Date = MAX(Orders[created_at])
Days Without Order Number = DATEDIFF([Last Order Date];MAX(DimDate[Date]);DAY)
Recency Note value =
SWITCH (
TRUE ();
[Days Without Order Number] < 7
&& [Last Order Date] <> BLANK (); 5;
[Days Without Order Number] >= 7
&& [Days Without Order Number] < 14; 4;
[Days Without Order Number] >= 14
&& [Days Without Order Number] < 30; 3;
[Days Without Order Number] >= 30
&& [Days Without Order Number] < 60; 2;
[Days Without Order Number] >= 60
&& [Days Without Order Number] < 90; 1;
0
)
Also created a dimension table with dates and another one with the clusters:
IDCluster
5 | Champions |
4 | Loyal |
3 | Potencial Clientes |
2 | Need Attention |
1 | Almost Lost |
0 | Lost |
Now add the following measure:
Cluster_Count =
VAR temptable =
SUMMARIZE ( Clients; Clients[id]; "Recency"; [Recency Note value] )
RETURN
IF (
MAX ( DimDate[Date] ) <= MIN ( Orders[created_at] );
BLANK ();
CALCULATE (
COUNTROWS ( FILTER ( temptable; [Recency] IN VALUES ( 'Cluster'[ID] ) ) )
)
)
See result below:
The two slicers on the bottom are for the order dates and for the date dimension table that will allow to have filters. these two slicers are sincronized with each other.
Check file attach, and if you need any further assistance please tell me.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHey @MFelix
Thank you very much for your help, I sent a mock up file just to understand the solution, but actually there is others parameters to identify a cluster. I just did not understand the Cluster Count part, I am sharing another power bi file with you, with all the parameters that actually counts.
Just for you to follow, I have the recency note, the frequency note and other parameters like (first date order, amout of orders) that I used IF Conditional to create each one of the clusters inside the Clients Table.
Would you mind to have a look at this new file, it has the same idea, but it is a bit more complex.
Thank you very much in advance.
I saw that you come from Portugal, this other file is in Portuguese, because actually this file comes from Brazil haha
Please let me know if you can understand the tables, the measures and the calculated columns
https://drive.google.com/file/d/1oTaTjwouSF46nYiAxzeq3L5ueiirrgzI/view?usp=sharing
Hi @Anonymous ,
Big file, I will have a look at it and will get back to you ASAP.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix thank you very much!!
I think it will be a bit harder to come up with a solution because there are actually too many variables to determinate all the clusters
Hey @MFelix !!
Yes of course, here it goes.
https://drive.google.com/file/d/14ZTb1Dzr3vhmg2MAmBW2WDaYr0RIYDJO/view?usp=sharing
If you look inside the file, there is going to be 3 tables (Clients, Orders and Store)
I created a calculed column inside Clients to determinate the cluster for each one of the clients based on the other calculed column days without order.
I would like to track throught time how the cluster are increasing or decreasing
Thank you very much for your reply.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |