0

I'm trying to build a Retention Analysis piece on a Power BI dashboard and would like some help in diving deeper into the visuals I've already built.

enter image description here

The green bars show the total customers served in each month and the black bars show the number of lapsed customers served in each month. Both numbers are based on the count of unique customer IDs in each month. However, I would like to be able to click on a given month in the graph and view the names of the lapsed customers in another visual (table, matrix, or otherwise).

The names themselves cannot be used to get the number of customers served or the lapsed customers because the name field is not a unique identifier.

However, when I click on any black bar in any given month and have the names/unique IDs appear on another table, it always returns the names of the total customers served in that month, and not the lapsed count.

I also tried showing only the lapsed number of customers appear on a separate graph shown below but when I try the same approach on that graph, the names of the total customers served are returned.

enter image description here

This is the formula I used to calculate the number of lapsed customers in a month -

Lost Customers 2 = 

VAR Before6 =
    CALCULATETABLE (
        VALUES ('Master File'[UniqueIDFinal]),
        PARALLELPERIOD ( 'Calendar Table'[Date], -3, MONTH )
    )
VAR During6 =
    CALCULATETABLE (
        VALUES ('Master File'[UniqueIDFinal]),
        DATESINPERIOD (
            'Calendar Table'[Date],
            EOMONTH ( MIN ('Master File'[Sale Date II]), 0 ),
            -3,
            MONTH
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ('Master File'[UniqueIDFinal]),
        FILTER (
            ALL ('Master File'),
            'Master File'[UniqueIDFinal] IN Before6
                && NOT 'Master File'[UniqueIDFinal] IN During6
        )
    )*-1

Calendar Table is an independent Calendar table created in my Power BI model.

In a nutshell - I would like to show the names of the customers that lapsed in any given month based on my lapsed customer count, along with some other details like how much money they spent, etc.

Thanks in advance.

beginnerprogrammerforever
  • 77
  • 5
  • The interaction among the visuals does not work in the way you assume here. When you click on the black bar of any given month in your visual, you are selecting the given month as filter, not the resulting value indicated by the black bar. For that matter, it does not matter whether you click on the green or the black bar of a given month: the filtering you get from this visual that can be used for the other visuals is the date/month. Where does the formula you are using come from? – Anonymous May 25 '23 at 00:05
  • I understand your point. How then do I get a list of the UniqueIDFinals that belong to the black bar in the first graph (and the green bar in the second graph)? I'm not sure what you mean by your question - it is a measure I created in the main table of my model. – beginnerprogrammerforever May 25 '23 at 14:25
  • 1
    Sorry I asked about the measure. It was not clear whether it was fully understood: 1. the measure is a number (count) based on “some” filtering. How should it know which are the related uniqueID after the aggregation? 2. The measure filtered the desired uniqueID before counting. So, actually the “filtered list” is on hand already. You need to create a similar measure to include the related names and e.g. concatenate them into one string instead of the counting. 3. The measure may not provide the result as expected (depending on the underlying definition of retention/lapsed). Good luck! – Anonymous May 25 '23 at 15:07

0 Answers0