Forum Discussion

LelaRc's avatar
LelaRc
New Member
4 years ago
Solved

Conversion rate between stages

Hey guys.

I need help with this solution. ðŸ˜¥

I have a sales funnel and I would like to calculate the conversion rate between stages.

So the idea would be to add a new column with the conversion rate of lead counts from SAL stage to SQL for example. I've always done this type of calculation but in the context of date, using previous month for example.

In my head the solution seems simple, but I couldn't find a working measure.

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  LelaRc ,

    Here are the steps you can follow：

    1. Create calculated column.

    conversao =
var _Index=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])))
var _Index1=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])-1))
var _Index2=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])-2))
return
IF('Table'[Index] in {7,9},DIVIDE(_Index,_Index2),DIVIDE(_Index,_Index1))

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

5 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    LelaRc 

    Please explain how to calculate the coversion rate. Give an example

    • LelaRc's avatar
      LelaRc
      New Member
      4 years ago

      It would be a simple division of the current step, over the previous step, according to the index. For example, it should have this result.

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        LelaRc 
        Great! Can you please provide sample dummy or insensitive data or otherise provide screenshots or your tables (not matrix report) and your data model with the relationships if you have any.
        have a great day!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  LelaRc ,

    Here are the steps you can follow：

    1. Create calculated column.

    conversao =
var _Index=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])))
var _Index1=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])-1))
var _Index2=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])-2))
return
IF('Table'[Index] in {7,9},DIVIDE(_Index,_Index2),DIVIDE(_Index,_Index1))

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • LelaRc's avatar
      LelaRc
      New Member
      4 years ago

      It worked perfectly, thank you very much for your help. 😄