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. ...
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.

     

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.