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,

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

    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)

    …DatePageOrder (conditional column)Count
     01/01/2016………
     01/01/2016page100005678
     01/01/2016step11070
     01/01/2016step22048
     01/01/2016page100004321
     01/01/2016page100001234
     01/01/2016step33046
     01/01/2016page100009887
     01/01/2016step44034
     01/01/2016page10000347834
     01/01/2016step55026
     01/01/2016page100006493
     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'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