Cummulative count for funnel visual

[Anonymous]

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!

[amitchandak · Accepted solution]

@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] )
)
)

[amitchandak · Accepted solution]

@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] )
)
)

[Anonymous]

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!

[v-rzhou-msft · Accepted solution]

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.