Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating a rolling difference over integer categories

Dear PowerBI Community,   I am having trouble with a seemingly easy task: I need to calculate a percentage of the customers lost after each sales step. Each solution I could find referred to a date...
  • Greg_Deckler's avatar
    6 years ago

    That's page 198 of my book, Crafting a funnel drop-off rate, it goes something along the lines of:

     

    Drop Off Rate = 
        VAR __CurrentStep = MAX([Step])
        VAR __PreviousStep = __CurrentStep - 1
        VAR __CurrentCount = COUNTROWS('R01_Table')
        VAR __PreviousCount = 
            IF(
                __CurrentStep = 1 , 
                0 , 
                COUNTROWS(FILTER(ALL('R01_Table'),[Step] = __PreviousStep ))
            )
    RETURN
        DIVIDE(__CurrentCount - __PreviousCount, __PreviousCount, 0)

     

    There is also Abandoment Rate:

    Abandonment Rate = 
        VAR __CurrentStep = MAX([Step])
        VAR __PreviousStep = 1
        VAR __CurrentCount = COUNTROWS('R01_Table')
        VAR __PreviousCount = 
            COUNTROWS(FILTER(ALL('R01_Table'),[Step] = __PreviousStep ))
    RETURN
        DIVIDE(__CurrentCount - __PreviousCount, __PreviousCount, 0)

     

  • v-kelly-msft's avatar
    6 years ago

    Hi Anonymous ,

     

    You wanna calculate the differences in steps,right?

    Then use below dax expression to create a measure:

     

    Measure = 
    var a =CALCULATE(MAX('Table'[Unique customers]),FILTER(ALL('Table'),'Table'[step number ]=MAX('Table'[step number ])-1))
    Return
    DIVIDE(MAX('Table'[Unique customers])-a,a)+0

     

    And you will see:

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!