Need Help with RFM Analysis in Power BI - Creating Summary Matrix with What-If Parameters

[HancyChang]

Need Help with RFM Analysis in Power BI - Creating Summary Matrix with What-If Parameters

Background

I'm developing an RFM-themed BI dashboard using member master data as the primary data source.

Current Dashboard Layout

• Violin plots for R, F, and M values
• Color-coded boxes below violin plots showing median, average, and custom values
• White input boxes for users to manually enter custom RFM values
• Bottom matrix displaying Member ID, actual RFM values, RFM scores, and RFM segments

RFM Scoring Logic

The RFM scoring is based on custom values with rolling calculations:

• R Score: If a member's R value (e.g., 149 days) is higher than the custom R value (e.g., 90 days), they get a score of 0. If lower, they get 1.
• F & M Scores: If higher than custom values, they get 1 point. If lower, they get 0.

Current Setup

• The white input boxes are created using What-If Parameters
• RFM scores and RFM segments are written as measures

Problem

I want to calculate member count and member count percentage by RFM segments, but since both RFM scores and RFM segments are measures, I cannot use them as dimensions in a matrix.

I initially tried to add RFM scores and RFM segments as calculated columns in the member master table, but I discovered that What-If Parameter values cannot be referenced in calculated columns.

Request

I will provide screenshots of my BI dashboard and the measures I'm using. Could experts please help me find a solution to:

1. Create a summary matrix showing member count and percentage by RFM segments
2. Make this work with What-If Parameters for dynamic custom value input

Thank you for your assistance!

[Anonymous · Accepted solution]

Hi @HancyChang ,
Thank you for reaching out to us on Microsoft Fabric Community Forum!

You're correct that measures cannot be used as dimensions in a matrix visual, which makes it challenging to summarize counts by RFM segment dynamically when using What-If Parameters.To create a dynamic summary matrix with member count and percentage by RFM segment, while still using What-If Parameters, here is a suggested workaround using disconnected RFM segment tables and supporting measures.
Please find the below steps.
1.After loading data into power bi,create 3 parameters.

• RecencyThreshold: Whole Number, Min = 0, Max = 200, Increment = 10, Default = 90

• FrequencyThreshold: Whole Number, Min = 0, Max = 20, Increment = 1, Default = 5

• MonetaryThreshold: Whole Number, Min = 0, Max = 2000, Increment = 50, Default = 500

• Adds slicers for your Parameters.

2.Create RFM Segment Measure using below:

RFM Segment =

SWITCH(

    [RFM Score],

    111, "Champions",

    011, "Loyal Customers",

    101, "At Risk",

    001, "Hibernating",

    100, "New Customers",

    "Others"

)

3.Create a Static Table for RFM Segments(please refer the pbix attached)

4.Create 2 mesaures Member Count and % Measures.
Member Count =
CALCULATE(
COUNTROWS('MemberMaster'),
FILTER('MemberMaster', [RFM Segment] = SELECTEDVALUE(RFMSegments[Segment]))
)


Member % =
DIVIDE(
[Member Count],
CALCULATE(COUNTROWS('MemberMaster')),
0
)

5.Add a matrix visual.In rows, add RFMSegments[Segment]. In values, add [Member Count], [Member %].
Please refer the attached screenshot and file for your reference:

I hope this helps.If so,give us kudos and consider accepting it as solution.
Thank you.

Regards,
Pallavi.