Funnel Form step analysis Calculated Metric DAX

[KamphuisB]

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

[Anonymous · Accepted solution]

@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

[KamphuisB]

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

[Anonymous]

@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

[KamphuisB]

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

[Anonymous]

@KamphuisB,

I have sent your modified PBIX file via Private Message.

Regards,

Lydia

[analyticsmsk]

It's ok, that you sent private message,

but what is the solution?

[KamphuisB]

Hi @analyticsmsk ,

Indeed I should have placed the final answer here. It has been a while but I recreated the scenario for you.

In my previous reply you can see that the dataset contains pages in between so you are not able to do a EARLIER or MIN(INDEX) - 1 because it could also be index - 3 etc. 

There was only 1 solution for me in which I needed to add a conditional order column based on the steps(pages) that I have in my dataset. So I did stepA = 10, stepB = 20, etc

So based on the example the dataset looks like this:

With that placed I created the following calculated metric:

Step/pStep_adjusted = 
var previous = 
CALCULATE(FIRSTNONBLANK('Table'[Count];'Table'[Count]);FILTER(ALLSELECTED('Table');'Table'[Order (conditional column)]=MIN('Table'[Order (conditional column)])-10))
return
IF(DIVIDE(SUM('Table'[Count]);previous)=BLANK();1;DIVIDE(SUM('Table'[Count]);previous))

In order to have the data that I needed:

Kind regards,

Bas Kamphuis

[Anonymous]

@KamphuisB,

Please share your PBIX file via Private Message if the DAX still don't work.

Regards,
Lydia