Forum Discussion
Funnel Form step analysis Calculated Metric DAX
KamphuisB,
Add an index column in your table in Query Editor of Power BI Desktop.
Then create the following columns in your table.Step/step1 = DIVIDE(Table[Count], CALCULATE(SUM(Table[Count]),FILTER(Table,Table[Steps]="A")))
Step/previous step = var previous= CALCULATE(FIRSTNONBLANK(Table[Count],Table[Count]),FILTER(Table,Table[Index]=EARLIER(Table[Index])-1) ) return IF(ISBLANK(previous),1, divide(Table[Count],previous))
Regards,
Lydia
H Lydia Anonymous,
Thank you for your response! Step/Step1 didn't work right away, but i was able to fix it by adding ALLSELECTED().
Step/Pstep does not work, because my dataset looks a bit different and i forgot to mention that. Sorry about that.
My data set is like this: (... are other columns or rows)
| … | Date | Page | Order (conditional column) | Count |
| 01/01/2016 | … | … | … | |
| 01/01/2016 | page | 10000 | 5678 | |
| 01/01/2016 | step1 | 10 | 70 | |
| 01/01/2016 | step2 | 20 | 48 | |
| 01/01/2016 | page | 10000 | 4321 | |
| 01/01/2016 | page | 10000 | 1234 | |
| 01/01/2016 | step3 | 30 | 46 | |
| 01/01/2016 | page | 10000 | 9887 | |
| 01/01/2016 | step4 | 40 | 34 | |
| 01/01/2016 | page | 10000 | 347834 | |
| 01/01/2016 | step5 | 50 | 26 | |
| 01/01/2016 | page | 10000 | 6493 | |
| 02/01/2016 | … | … | … |
As followed for the example in my first post:
Like i told at the start of this post: Step/step1 is working:
Step/Step1 = DIVIDE(SUM(Table[Count], CALCULATE([SUM(Table[Count],FILTER(ALLSELECTED(Table),Table[Steps] = "A)))
The formula Step / Previous Step is not working for the visualisation in the first post and the above dataset. First i think there is an typo in your calculation, correct? Cause this was not possible.
IF(ISBLANK(previous),1, divide(Table[Count],previous))
I have changed it to
Step/pStep =
var previous =
CALCULATE(FIRSTNONBLANK(Table[Count],Table[Count),FILTER(Table,Table[Index]=EARLIER(Table[Index)-1))
return
IF(ISBLANK(previous),DIVIDE(SUM(Table[Count]),previous))
But then i get a error that EARLIER is not possible because it refers to an earlier row context which doesn't exist. I have changed EARLIER() to MAX() but that didn't work as well.
Appreciate your help!
Kind regards,
Bas Kamphuis
KamphuisB,
Please firstly add an index column in your table. Then right click your table and select "New Column" to apply the DAX formula.
Regards,
Lydia
Sorry but step/pstep did not work as a calculated column in my case.. Last weeks i was on holiday so i couldn't answer on this ticket. I will look again into this matter and try it somehow with a calculated metric.
kind regards,
Bas Kamphuis
