Forum Discussion

cliu822's avatar
cliu822
New Member
3 years ago
Solved

Count value before reaching a certain value for a funnel chart

Hi everyone,   I am trying to create a funnel chart for 10 steps of a process. The funnel chart should show how many users have completed each of these 10 steps. I have an example table "Step Tabl...
Need Help
Show and Tell
  • bolfri's avatar
    bolfri
    3 years ago

    Create a new column in the user table:

     

    Max Completed Step =
    var current_user = FactUsers[UID]
    var max_step = CALCULATE(MAXX(VALUES(FactUsers[UID]),MAX(FactUsers[Steps])),ALL(FactUsers),FactUsers[UID]=current_user,FactUsers[Value]=1)
    var step_level = LOOKUPVALUE(dim_steps[Level of Step],dim_steps[Name of Step],max_step)
    var previous_step_level = step_level-1
    return IF(step_level <= 5, previous_step_level,step_level)
     
    This column holds an information about max completed step by it's user. It's also has a logic for steps 1-5 to reduce the steps for rows with 1.
     
    Then create a measure:
     
    Number of users =
    var current_step = MAX(dim_steps[Level of Step])
    return CALCULATE(DISTINCTCOUNT(FactUsers[UID]),FactUsers[Max Completed Step] >= current_step)
     
    Result:

     

cliu822's avatar
cliu822
New Member
3 years ago

bolfri 

Thank you so much for the reply. There is just a small problem, that the "Steps" i.e. "Name of Step" are more complicated than that. It is actually more like below:

Name of StepsStep DescriptionLevel of Step
StoppedbgdfsStopped at bgdfs1
StoppedhgjkgStopped at hgjkg2
StoppedahaghStopped at ahagh3

 

In this way, how can I adjust the variable max_step? Because MAX() can not find the maximum step anymore if the name is actually some randome words.

 

Thank you very much again!

  • cliu822's avatar
    cliu822
    New Member
    3 years ago

    I have figured this out, in the fact table I added a column which convert the name of steps to "Step 1,2,3 etc.", and then use MAX() on this column. Thank you so much for your answer, it works fine now!