Forum Discussion
hi
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
I know its bit complex but please help if possible
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.
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" ) )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.thxTo 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.
123abc ,
thanks for your nice and very clear reply. it really helpfull but one thing i want to mention, here
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
"R Value", [R value],
R Score"F Value", [F value],"M Value", [M value (sale)],F Score"Cell No Value", [Cell No value],M ScoreCell No scorei think we are alomost achieve. please help. I have attached an excel file with Invoice_no, Invoice_dt and Amount as R, F, M value and score
Not sure how 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.
Pleae help me
i want something like this, following ones are not correct
By customer_code
By mobile_no
thx
