hi

Question

Hello,
&nbsp;
I have this RFM table
&nbsp;
RFM Table =
SUMMARIZE('bm_retail_t ssummary','bm_retail_t ssummary'[CUSTOMER_CODE],'bm_retail_t ssummary'[mobile_no],"R Value", [R value],"F Value", [F value],"M Value", [M value (sale)],"Cell No Value", [Cell No value],)
&nbsp;
here
R value is last invoice date
F value is count of invoice number
M value is quantity purchased
and cell no value is count of mobile number
&nbsp;
using these 4 fields i want to create RFM analysis. usially RFM analysis is for&nbsp;
R value is last invoice date
F value is count of invoice number
M value is quantity purchased&nbsp; &nbsp;but i want to use Cell no Value as 4th in 1 RFM table
&nbsp;
'bm_retail_t ssummary','bm_retail_t ssummary'[CUSTOMER_CODE],'bm_retail_t ssummary'[mobile_no],
&nbsp;
here i cant use Customer_code and Mobile_no column same time. either i have to use&nbsp;
&nbsp;
RFM Table =
SUMMARIZE('bm_retail_t ssummary','bm_retail_t ssummary'[CUSTOMER_CODE],"R Value", [R value],"F Value", [F value],"M Value", [M value (sale)],"Cell No Value", [Cell No value],)
&nbsp;
or
&nbsp;
RFM Table =
SUMMARIZE('bm_retail_t ssummary','bm_retail_t ssummary'[mobile_no],"R Value", [R value],"F Value", [F value],"M Value", [M value (sale)],"Cell No Value", [Cell No value],)
&nbsp;
can i use switch or any other function a single RFM table?
&nbsp;
thx

123abc · Answer

In Power BI, you can create a single RFM (Recency, Frequency, Monetary) table that combines data from different columns using the SWITCH or IF function along with calculated columns or measures. Since you want to use "Cell No Value" as the 4th dimension in your RFM analysis alongside "R Value," "F Value," and "M Value," you can create a calculated column or measure to achieve this. Here's how you can do it:

First, create a calculated column for RFMC (Recency, Frequency, Monetary, Cell No) in your 'bm_retail_t ssummary' table:

RFMC =
SWITCH(
TRUE(),
NOT(ISBLANK('bm_retail_t ssummary'[R Value])) && NOT(ISBLANK('bm_retail_t ssummary'[F Value])) && NOT(ISBLANK('bm_retail_t ssummary'[M Value])) && NOT(ISBLANK('bm_retail_t ssummary'[Cell No Value])),
"RFMC",
NOT(ISBLANK('bm_retail_t ssummary'[R Value])) && NOT(ISBLANK('bm_retail_t ssummary'[F Value])) && NOT(ISBLANK('bm_retail_t ssummary'[M Value])),
"RFM",
NOT(ISBLANK('bm_retail_t ssummary'[R Value])) && NOT(ISBLANK('bm_retail_t ssummary'[F Value])),
"RF",
NOT(ISBLANK('bm_retail_t ssummary'[R Value])),
"R",
BLANK()
)

This calculated column checks the presence of values in each of the R, F, M, and Cell No columns and assigns a label accordingly.

Now, you can use this "RFMC" calculated column in your visuals and slicers to perform RFM analysis when all four values are present, or switch to the desired RFM analysis when one or more values are missing.

For example, you can create measures for Recency, Frequency, Monetary, and Cell No Value based on the selected "RFMC" category in your visuals.

Remember to adjust your visuals and measures accordingly to accommodate this "RFMC" category.

This approach allows you to create a single RFM table that adapts to the availability of data in the columns and provides you with the flexibility you need in your analysis.

If I answered your question, please mark my post as solution, Appreciate your Kudos

abc_777 · Answer

&nbsp;
I know its bit complex but please help if possible
&nbsp;
When i create this table i have to make relation between ssummary table customer_code with RFM Test Table Customer_code as active and ssummary table mobile_no with RFM Test Table mobile_no as inactive
otherwise its not filtering.
and relation is many to many.&nbsp;
&nbsp;

RFM Test Table =
SUMMARIZE( 'bm_retail_t ssummary',
'bm_retail_t ssummary'[CUSTOMER_CODE],
'bm_retail_t ssummary'[mobile_no],
"R Value", [R value],
"F Value", [F value],
"M Value", [M value (sale)],
"Cell No Value", [Cell No value],
"Summary Type",
SWITCH( TRUE(),
ISINSCOPE('bm_retail_t ssummary'[CUSTOMER_CODE]), "Customer Summary",
ISINSCOPE('bm_retail_t ssummary'[mobile_no]),
IF(ISINSCOPE('bm_retail_t ssummary'[CUSTOMER_CODE]),
"Mobile No Summary", "Mobile No Summary using Inactive Relationship" ), "Other" ) )
&nbsp;
i want to make a many to one relation between ssummary table with RFM test Table with either Customer_code or Mobile_no and want to filter RFM by Customer_Code in one chart and RFM by Mobile_no in another chart.
&nbsp;
thx
&nbsp;

123abc · Answer

To create a many-to-one relationship between your 'ssummary' table and the 'RFM Test Table' using either 'Customer_Code' or 'Mobile_no,' and then filter RFM by 'Customer_Code' in one chart and RFM by 'Mobile_no' in another chart, you can follow these steps:

Create a new relationship using 'Customer_Code':
- Create a new calculated column in your 'RFM Test Table' that uses the 'Customer_Code' from 'ssummary.' Name it something like 'Related Customer_Code.'
- Create a relationship between the 'Related Customer_Code' column in the 'RFM Test Table' and the 'Customer_Code' column in the 'ssummary' table. Set this relationship as active.
Create a new relationship using 'Mobile_no':
- Create a new calculated column in your 'RFM Test Table' that uses the 'Mobile_no' from 'ssummary.' Name it something like 'Related Mobile_no.'
- Create a relationship between the 'Related Mobile_no' column in the 'RFM Test Table' and the 'Mobile_no' column in the 'ssummary' table. Set this relationship as inactive.

Here's an example of how to create the calculated columns for both relationships in the 'RFM Test Table':

Related Customer_Code =
IF ( HASONEVALUE ( 'ssummary'[CUSTOMER_CODE] ), VALUES ( 'ssummary'[CUSTOMER_CODE] ) )

Related Mobile_no =
IF ( HASONEVALUE ( 'ssummary'[mobile_no] ), VALUES ( 'ssummary'[mobile_no] ) )

Create your two charts:
- In one chart, use the 'RFM Test Table'[Related Customer_Code] as the dimension to filter the RFM analysis by 'Customer_Code.'
- In the other chart, use the 'RFM Test Table'[Related Mobile_no] as the dimension to filter the RFM analysis by 'Mobile_no.'
- Make sure to include the 'R Value,' 'F Value,' 'M Value,' and 'Cell No Value' measures in both charts for your RFM analysis.

With these two relationships and calculated columns, you can now filter your RFM analysis by 'Customer_Code' in one chart and by 'Mobile_no' in another chart. The relationships should automatically switch between active and inactive based on your selections.

abc_777 · Answer

123abc&nbsp;,
&nbsp;
thanks for your nice and very clear reply. it really helpfull but one thing i want to mention, here&nbsp;
followings are in calculative column as well along with Customer_code and Mobile_no. as my formula i not working so i disabled customer_code here but i want to use both Cusotmer_code and Mobile_no
&nbsp;
"R Value", [R value],

R Score

"F Value", [F value],

F Score

"M Value", [M value (sale)],

M Score

"Cell No Value", [Cell No value],
Cell No score
&nbsp;

&nbsp;
i think we are alomost&nbsp; achieve.&nbsp;please help. I have attached an excel file with Invoice_no, Invoice_dt and Amount as R, F, M value and score
&nbsp;
Not sure how&nbsp; filter will work when i use inactive relation for mobile_no when i use filter by calendar or customer Segment (champion, loyal, potential, etc etc...). I am still not clear this part.
&nbsp;
Pleae help me
i want something like this, following ones are not correct&nbsp;
&nbsp;
By customer_code

By mobile_no
&nbsp;

&nbsp;
&nbsp;
&nbsp;
thx

123abc · Answer

I understand that you want to incorporate the score columns for each metric ('R Score,' 'F Score,' 'M Score,' 'Cell No Score') into your RFM analysis, and you're concerned about how filtering will work when using an inactive relationship for 'mobile_no' and filtering by other attributes like calendar or customer segments.To achieve your goal of using both 'Customer_code' and 'mobile_no' in your RFM analysis while considering the scores, and to ensure filtering works correctly with other attributes, you can follow these steps:Create Separate Measures for RFM Metrics with Scores:Create separate measures for each RFM metric that incorporate the corresponding score. Here's an example for 'R Value' with 'R Score':R Value with Score = [R value] + [R Score]&nbsp;Repeat this process for 'F Value,' 'M Value,' and 'Cell No Value' with their respective scores.Create Separate RFM Measures for 'Customer_code' and 'mobile_no':Create two sets of RFM measures, one set for 'Customer_code' and another for 'mobile_no.' These measures will use the corresponding RFM metrics with scores. For 'Customer_code,' use the 'CUSTOMER_CODE' field, and for 'mobile_no,' use the 'mobile_no' field.For example, for 'Customer_code':RFM Customer Code =CALCULATE([R Value with Score] + [F Value with Score] + [M Value with Score] + [Cell No Value with Score],FILTER('bm_retail_t ssummary', 'bm_retail_t ssummary'[CUSTOMER_CODE] = MAX('bm_retail_t ssummary'[CUSTOMER_CODE])))&nbsp;For 'mobile_no':&nbsp;RFM Mobile No =CALCULATE([R Value with Score] + [F Value with Score] + [M Value with Score] + [Cell No Value with Score],FILTER('bm_retail_t ssummary', 'bm_retail_t ssummary'[mobile_no] = MAX('bm_retail_t ssummary'[mobile_no])))&nbsp;Create Two Visuals:Create two separate visuals, one for 'Customer_code' and another for 'mobile_no.' Use the corresponding RFM measures ('RFM Customer Code' and 'RFM Mobile No') in these visuals.Create Filters or Slicers:To filter the visuals by calendar or customer segments, create slicers or filters for those attributes, and place them on the report canvas. These slicers or filters should work seamlessly with both visuals, allowing you to filter by other attributes while seeing the RFM analysis with scores for both 'Customer_code' and 'mobile_no.'By following these steps, you can incorporate the score columns into your RFM analysis and ensure that filtering works correctly when using inactive relationships for 'mobile_no' and other attributes... i will check your pbix file ,

Forum Discussion

hi

6 Replies