Forum Discussion
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, 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
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)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)+0And you will see:
For the related .pbix file,pls click here.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
4 Replies
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)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)+0And you will see:
For the related .pbix file,pls click here.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!