Forum Discussion
Funnel Form step analysis Calculated Metric DAX
Hi all,
Currently i am working on a dashboard and i want to make a dynamic table in which i can show a form funnel(based on choice in slicer and how it performs. I can't figure out how to do the two red calculated metrics below.
| Slicer | Dynamic Table | ||||
| Form A | Steps | Count | step / previous step | step / step 1 | |
| Form B | A | 70 | 100% | 100% | |
| Form C | B | 48 | 69% | 69% | |
| C | 46 | 96% | 66% | ||
| D | 34 | 74% | 49% | ||
| E | 26 | 76% | 37% |
- Step / Previous step
I thougt something like DIVIDE( SUM(Table[Count]) , CALCULATE(SUM(Table[Count]),FILTER(ALL(Table),Table[Steps] = MAX(Table[Steps])-1)) ) was working for this calculation but to bad... It didn't work this time.
- Step / Step 1
Here i thought this would work: DIVIDE( SUM(Table[Count]) , CALCULATE(SUM(Table[Count]), Table[Steps] = "A") ) but that wasn't working either...
I'm out of options that i know, so i would really appreciate if someone could help me with this. I have also checked https://community.powerbi.com/t5/Desktop/DAX-Calculation-Percent-of-First-or-Max-Value/td-p/242535 but couldn't get the answers from there working..
Hope to hear from you!
Thanks,
Bas
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
8 Replies
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,
LydiaH 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
