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!
Anonymous
6 years agoNot applicable
Hey Kelly,
thank you for the swift reply - will try it and come back with feedback asap!
Best,
Andrei
Anonymous
6 years agoNot applicable
Greg and Kelly,
thank you very much! The suggested measures worked perfectly!
Have also learned from the suggestions!
Best,
Andrei