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:

     

bolfri's avatar
bolfri
Icon for Solution Sage rankSolution Sage
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: