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,

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's avatar
KamphuisB
Frequent Visitor
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    KamphuisB,

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

    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