Forum Discussion
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 Table" for the 10 steps as follows:
| Name of Step | Description of Step | Level of Step |
| Step 1 | Stopped at Step 1 | 1 |
| Step 2 | Stopped at Step 2 | 2 |
| Step 3 | Stopped at Step 3 | 3 |
| Step 4 | Stopped at Step 4 | 4 |
| Step 5 | Stopped at Step 5 | 5 |
| Step 6 | Success at Step 6 | 6 |
| Step 7 | Success at Step 7 | 7 |
| Step 8 | Success at Step 8 | 8 |
| Step 9 | Success at Step 9 | 9 |
| Step 10 | Success at Step 10 | 10 |
There is another table called "User Table". In this table, the column "Steps" contains all the steps for each user, "Value" contains the information if a user complete the step or not. However, the value 0 or 1 serves as "No" and "Yes", so for the first 5 steps containing "Stopped" in the description, a 0 means this user didn't stop at this step. For the other 5 steps containing "success" in the description, the value 1 means he did succed at this step.
An example "User Table" can be found as follows:
| Date | UID | Steps | Value |
| 2022-01-02 | 12345 | Step 2 | 0 |
| 2022-01-02 | 12345 | Step 3 | 0 |
| 2022-01-02 | 12345 | Step 5 | 0 |
| 2022-01-02 | 12345 | Step 4 | 1 |
| 2022-01-02 | 12345 | Step 1 | 0 |
| 2022-01-02 | 12345 | Step 6 | 0 |
| 2022-01-02 | 12345 | Step 8 | 0 |
| 2022-01-02 | 12345 | Step 7 | 0 |
| 2022-01-02 | 12345 | Step 9 | 0 |
| 2022-01-02 | 12345 | Step 10 | 0 |
| 2022-03-04 | 67890 | Step 1 | 0 |
| 2022-03-04 | 67890 | Step 4 | 0 |
| 2022-03-04 | 67890 | Step 5 | 0 |
| 2022-03-04 | 67890 | Step 7 | 1 |
| 2022-03-04 | 67890 | Step 3 | 0 |
| 2022-03-04 | 67890 | Step 2 | 0 |
| 2022-03-04 | 67890 | Step 6 | 1 |
| 2022-03-04 | 67890 | Step 8 | 1 |
| 2022-03-04 | 67890 | Step 9 | 0 |
| 2022-03-04 | 67890 | Step 10 | 0 |
In this example, User 12345 stopped at Step 4 while user 67890 stopped at Step 9. So For Step 1-3 there are 2 users completed these steps. For Step 1 user has completed it, for step 5-8 1 user has completed them, for step 9-10 nobody has completed it.
The trick here is, for step 1-5, when a user stopped at step 4 (value = 1), the value for his step 5 will still be 0, because he didn't stop at step 5 (He didn't even start it!), because of this tricky part, I don't know how to write metric for this funnel chart. I will really appreciate any answer!
Thanks!
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-1return 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:
3 Replies
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-1return 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: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 Steps Step Description Level of Step Stoppedbgdfs Stopped at bgdfs 1 Stoppedhgjkg Stopped at hgjkg 2 Stoppedahagh Stopped at ahagh 3 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!
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!
