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, o...
  • 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

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