Forum Discussion
Anonymous
6 years agoNot applicable
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...
- 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) - 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)+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!
Greg_Deckler
6 years agoCommunity Champion
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)