Calculating a rolling difference over integer categories

[Anonymous]

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, however, in my case I have no date at all.

Here you see a simple unique count of the customer IDs that were completed in a respective sales step:

Now I need to calculate a difference in the count of unique values between two numeric integer categories (that are always increasing and never miss a number). I.e. the values by each category presented above would look like 0% - 33% - 0% - 50% - 66%.

Link to a file on drive does not seem to work, so I attach the screenshots of the tables to demonstrate the underlying data (really sorry for the inconvenience):

I have tried a couple of measures but failed miserably.

I would highly appreciate you help and guidance on this one! 

Best,

Andrei

[Greg_Deckler · Accepted solution]

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 · Accepted solution]

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!

[Anonymous]

Hey Kelly,

thank you for the swift reply - will try it and come back with feedback asap!

Best,

Andrei

[Anonymous]

Greg and Kelly,

thank you very much! The suggested measures worked perfectly! 

Have also learned from the suggestions!

Best,

Andrei

[Greg_Deckler · Accepted solution]

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)