Forum Discussion

missdev's avatar
missdev
Regular Visitor
3 years ago
Solved

Help with Customer Behavior Tracking

Hey all!   I am trying to build a Power BI where we can show the aggregate behavior of our customers across different stages in our process. We have three steps to our process: Primary and Seconda...
Need Help
PaulDBrown's avatar
PaulDBrown
Community Champion
3 years ago

Can you post a depiction of the expected outcome?

missdev's avatar
missdev
Regular Visitor
3 years ago

What I am looking for is customer retention based on what the customer applied for as their first choice. So, if they applied for EP99999P, I need to see how many of those people participated in the other steps of the process. The chart might look something like this:

 

 

When "EP99999P" is people who applied for that at A.1 and "Other" is people who applied for anything else at A.1. It does not matter WHAT they applied for at A.2 and further, the ONLY thing that matters is what they applied for at A.1.

 

Everything I've been able to create will not filter A.2-C.1 based on the choice for A.1, which is what I need.

 

Thanks!

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    If you want to use measures, here is one way (with the independent product selection table - same model as I posted previously)

     

     

    Customer retention =
VAR _List =
    CALCULATETABLE (
        VALUES ( 'fTable'[Customer Key] ),
        FILTER (
            ALL ( fTable ),
            'fTable'[Product] = SELECTEDVALUE ( 'Product Sel'[SelProduct] )
                && ( 'fTable'[Step #] ) = 1
                && fTable[Choice] = 1
        )
    ) // Creates a table of all customer keys which have bought the selected product at step 1 and choice 1
VAR _Cust =
    VALUES ( 'Dim Customer'[dCustomer Key] ) //lists customer keys
VAR _FiltCust =
    INTERSECT ( _Cust, _List ) //Creates a table where the customer key is present in the _list table
VAR _Result =
    CALCULATE ( DISTINCTCOUNT ( fTable[Customer Key] ), _FiltCust ) // Calculates the distinctcount of customers present in _FiltCust table
RETURN
    _Result

    Other =
VAR _List =
    CALCULATETABLE (
        VALUES ( 'fTable'[Customer Key] ),
        FILTER (
            ALL ( fTable ),
            'fTable'[Product] <> SELECTEDVALUE ( 'Product Sel'[SelProduct] )
                && ( 'fTable'[Step #] ) = 1
                && fTable[Choice] = 1
        )
    )
VAR _Cust =
    VALUES ( 'Dim Customer'[dCustomer Key] )
VAR _FiltCust =
    INTERSECT ( _Cust, _List )
VAR _Result =
    CALCULATE ( DISTINCTCOUNT ( fTable[Customer Key] ), _FiltCust )
RETURN
    _Result

     

     I've attached the new file