Forum Discussion

HancyChang's avatar
HancyChang
Frequent Visitor
1 year ago
Solved

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

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!

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.