conversion rate over time

[MikeK]

Hi,

I have to visualize the conversion rate of leads over time.

In my leads table i have mutliple dates per lead. One is the creation date, another one ist the won date, if the lead is won, otherwise its blank.

Something like this:

LeadID         created         won

------------------------------------

1                 20171103     

2                 20171212       20180123

3                 20180122      

4                 20180202       20180202

What I have to create is a visual like this:

I added a column to my table, where I calculate the months between won and created dates.

Then used a matrix visual where columns are month and year from my dates dimension and rows are the newly added column with the months between created and won.

The percentage shown in each cell is always relative to the count of leads in this month.

I tried it with various combinations of a measure with the following formula:

IF(
    CALCULATE(COUNTA('FactLead'[LeadID]); ALLSELECTED('DimDate'[Month])) <> 0;
    CALCULATE(COUNTA('FactLead'[LeadID]); ALLSELECTED('DimDate'[Date])) / CALCULATE(COUNTA('FactLead'[LeadID]); ALLSELECTED('DimDate'[Month]));
    BLANK()
)

Obviously it didn't work as expected, otherwise I would not write here. 😉

The questions I have right now:

1. How can I calculate the correct percentage?

2. How can I fill up the blank cells when no new leads where won in this timeframe?
    For example: There was a sale within 6 months, but none within 7 months of this month, so the value of 6 months (the cell above) should be displayed. Something with CROSSJOIN( DateDim[Date]; FactLead[MonthsCreatedContract]) ?

Thank you in advance for your help. Hope this is also for you a little bit challenging!

All the best,

Mike

[MikeK · Accepted solution]

Hi @all

I solved at least point 1 in the meantime. For everyone who's interessted here is how I did it.

I added the following DAX as a measure and used the masure as value in a matrix component.

Lead-Deposit conversion over time = 
VAR TotalLeadsInMonth = 
CALCULATE(
    COUNTAX(
    FILTER(
        ALL(FactLead);
        MONTH(FactLead[DD_created]) = MAX(DimDate[Month]) && YEAR(FactLead[DD_created]) = MAX(DimDate[Year])
    );
    FactLead[LeadID]
))

VAR TotalDeposits = 
CALCULATE(
    COUNTAX(
    FILTER(
        ALLSELECTED(FactLead);
        MONTH(FactLead[DD_created]) = MAX(DimDate[Month]) 
        && YEAR(FactLead[DD_created]) = MAX(DimDate[Year])
        && FactLead[MonthsLeadDeposit] <= MAX(FactLead[MonthsLeadDeposit])
        && FactLead[MonthsLeadDeposit] <> BLANK()
    );
    FactLead[LeadID]
))
 
RETURN DIVIDE(TotalDeposits; TotalLeadsInMonth)

For now my customer is happy and I don't have to fill up the empty cells.

Hope it stays that way!

Best regards,

Mike

[v-juanli-msft]

Hi @MikeK

In this formula

IF(
    CALCULATE(COUNTA('FactLead'[LeadID]); ALLSELECTED('DimDate'[Month])) <> 0;
    CALCULATE(COUNTA('FactLead'[LeadID]); ALLSELECTED('DimDate'[Date])) / CALCULATE(COUNTA('FactLead'[LeadID]); ALLSELECTED('DimDate'[Month]));
    BLANK()
)

this formula means:

if a row of [won] is not blank, calculate the total rows of FactLead'[LeadID] for each date/calculate the total rows of FactLead'[LeadID] for the specific months.

Right?

Best Regards

maggie

[MikeK]

Hi maggie

I use the IF only to catch a possible division by zero before it happens. So the "real" calculation in this formula is in the true part of the function call.

So here's the formula without the IF:

CALCULATE(COUNTA('FactLead'[LeadID]); ALLSELECTED('DimDate'[Date])) / CALCULATE(COUNTA('FactLead'[LeadID]); ALLSELECTED('DimDate'[Month]))

If I understand the situation correct, this is what I try:

The dividend should be the count of leads won in the context of the cell. The context in the matrix is the amount of months since creation date (row) and the year/month (column)

The divisor should be the total count of leads in this month.

If I execute this devision and set the datatype to percentage, theoretically it should display the corret value.

Am I on the right track?

Best regards,

Mike