Forum Discussion

KamphuisB's avatar
KamphuisB
Frequent Visitor
8 years ago
Solved

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 ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

KamphuisB,

I have sent your modified PBIX file via Private Message.

Regards,

Lydia

analyticsmsk's avatar
analyticsmsk
Frequent Visitor
7 years ago

It's ok, that you sent private message,

but what is the solution?

 

 

 

  • KamphuisB's avatar
    KamphuisB
    Frequent Visitor
    7 years ago

    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