Forum Discussion
Cummulative count for funnel visual
Hello community!
I´m having a problem with the funnel visual because I need to make a cumulative count by stage for it to look properly. I´ve used a similar formula to the one provided in this post.
The problem that I´m having is that when I compare the funnel visual to a matrix grouped by stage the results are different and I can´t find the reason why
The table that I´m working with is ALLClients_Export which basically has one user per row and it´s current stage on the flow("ESTADO","StageId").
The other table is Funnel_Lookup where each stage is defined and has an ID assigned.
Funnel_Lookup ALLClients_Export
StageID 1:* StageID
The result that I´m looking for is basically the following:
but with the correct values and percentages shown on the following matrix ("En proceso" is included in "Todas las sesiones":
so it should look like:
| Stage | Count |
| Todas las sesiones | 1279 |
| Validacion telefono | 1255 |
| DNI Frontal | 1203 |
| DNI Dorso | 1203 |
| ... | ... |
| Aprobado | 1081 |
| Rechazado | 122 |
I really can´t figure out how to solve this, the formula I´m currently using is the following:
CuentaSKUporStatus =
CALCULATE (
COUNTA ( ALLClients_Export[ESTADO] ),
FILTER (
ALL ( Funnel_Lookup ),
Funnel_Lookup[Stageid] >= MIN ( ALLClients_Export[Stageid] )
)
)
Here´s the link for the .pbix
Thanks!
Anonymous , Can you please try like one of the two ways and check
CuentaSKUporStatus =
CALCULATE (
COUNTA ( ALLClients_Export[ESTADO] ),
FILTER (
ALL ( ALLClients_Export ),
ALLClients_Export[Stageid] >= MIN ( ALLClients_Export[Stageid] )
)
)or
CuentaSKUporStatus =
CALCULATE (
COUNTA ( ALLClients_Export[ESTADO] ),
FILTER (
ALL ( Funnel_Lookup ),
Funnel_Lookup[Stageid] >= MIN ( Funnel_Lookup[Stageid] )
)
)Hi Anonymous
You add ESTADO into the column in this matrix.
Firstly, you add Stage in Funnel_Lookup(you related stageid in ALLClients_Export to stageid in Funnel_Lookup) into the column in this matrix.
The Value 'Aprobados' in Stage in Funnel_Lookup will relate to stageid 10 in ALLClients_Export.
We can see if we filter stageid =10 in this table, ESTADO will contains three values.
Result:
Aprobado (value in Stage in Funnel_Lookup) = Aprobado+Aprobado+Error Smarter +Aprobado-Sin PDF (value in ESTADO in ALLClients_Export) = 1056+22+3=1081.
It means the result you got is correct.
If you want to show 1081 in Aprobado, you can add Stage into matrix column.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
Anonymous , Can you please try like one of the two ways and check
CuentaSKUporStatus =
CALCULATE (
COUNTA ( ALLClients_Export[ESTADO] ),
FILTER (
ALL ( ALLClients_Export ),
ALLClients_Export[Stageid] >= MIN ( ALLClients_Export[Stageid] )
)
)or
CuentaSKUporStatus =
CALCULATE (
COUNTA ( ALLClients_Export[ESTADO] ),
FILTER (
ALL ( Funnel_Lookup ),
Funnel_Lookup[Stageid] >= MIN ( Funnel_Lookup[Stageid] )
)
)The first option got really close and this is the result:
Every stage count is ok except for "Aprobados" which should be showing 1081. I don´t understand why this is the case.
The other solution seems accurate but I can´t filter it by month which would make the funnel pretty much useless.
Thank you very much for your help!
Hi Anonymous
You add ESTADO into the column in this matrix.
Firstly, you add Stage in Funnel_Lookup(you related stageid in ALLClients_Export to stageid in Funnel_Lookup) into the column in this matrix.
The Value 'Aprobados' in Stage in Funnel_Lookup will relate to stageid 10 in ALLClients_Export.
We can see if we filter stageid =10 in this table, ESTADO will contains three values.
Result:
Aprobado (value in Stage in Funnel_Lookup) = Aprobado+Aprobado+Error Smarter +Aprobado-Sin PDF (value in ESTADO in ALLClients_Export) = 1056+22+3=1081.
It means the result you got is correct.
If you want to show 1081 in Aprobado, you can add Stage into matrix column.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.