Forum Discussion

MikeK's avatar
MikeK
Regular Visitor
8 years ago
Solved

conversion rate over time

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's avatar
    MikeK
    8 years ago

    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

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    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's avatar
      MikeK
      Regular Visitor
      8 years ago

      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

  • MikeK's avatar
    MikeK
    Regular Visitor
    8 years ago

    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