How to make dynamic RFM scores that adapt according to a chosen period?

DAX DAX Calculations
1

Dear experts

Here, I’m trying to set up an RFM on Power BI. But the problem is that I want the label of the segment to be different depending on the period and/or product and/or recency in days. Or For example, to go from an annual label to a monthly label, I tried different ways but nothing works , so please help.

Example :
Please look at the screenshot Customer name : Roger Griffin ,

Case - 1
The customer considered Gold because the points are very high (Thats ok)

image
image1169×581 36 KB

Case-2

image
image1173×594 36.7 KB

Here after changing the period the segment should be (Rear customer) not gold because his reward is 144.

I need the data to reflect any other parameter chosen e.g.: products, recency in days , year and month.

I attached the pbix and db files if you want to trace the proposal.

Customer Data 1.3_RFM Trail _ English.pbix (1.5 MB)
Learning Summit Nov18 - Data Model.xlsx (3.9 MB)

2

@MAAbdullah47

I solved the problem in two ways. In one I use a measure with a SWITCH( TRUE() ) statement. Other is a more m-query way in combination with a LOOKUPVALUE. The latter introduced some errors in your visualizations. I fixed that for the most, but not for all, but I think you can fix it easily as well (if not, I can help you problaby on that one)

Everything I implemented has the name DM… & something.

I don’t know if you can follow my steps in the query editor. If not, just ask and I am happy to assist further.

I am not really sure whether this solution is called best-practice. So I invite anyone else who reads this to maybe verify my procedure that would be nice.

BTW, You are building a really nice dashboard!

See 2 attached files for the two different solutions.
Solution RFM 1.pbix (1.5 MB)
Solution RFM 2.pbix (1.5 MB)

3

Thank you so much @DashboardingMeesters I’ll check it and give you my feedback on it.

4

Hi @DashboardingMeesters

For the file (Solution RFM 1) it is clear for me , (Solution RFM 2) it might be more useful but I don’t know Can you give me an explanation of the solution I mean (Solution RFM 2) ?

5

Hi @MAAbdullah47 ,

Of course I can give you an explanation.

Below I will explain the steps I took

  1. Open power query editor and navigate to customer class sheet

  2. Replaced the 10.000.000 value with a more reasonable number of 10.000, since I assume it’s some sort of an upperlimit.(Step: ReplacedValue21)

  3. I make a list, which I would like to expand. This list contains every number between the lower and upperlimit of a certrain row. I created this with List.Numbers([Min] , [Max]-[Min], 1 ) (Step: Added Custom)

  4. Now I can expand this list to new rows, so we have a row for every value of the RFM score to which we can link. (Expanded DM key). Basically you could rmeove the min and max afterwards to clean up the model.

  5. Press Close & Apply and go to your report.

  6. In the report create a measure with LOOKUPVALUE().

  7. Last step, replace the errors which occorued due to the process exlained.

I think I have completed step 7 now (see attachement)
Solution RFM 2.pbix (1.6 MB)
. But be aware, I don’t understand the language used in your report. So, make sure to check yourself if I replaced everything correctly.

Hope this helps

BTW, your report seems to be very slow on my PC.

1 Like
6

Thank you @DashboardingMeesters I’ll practice it and get back to u.

1 Like