Forum Discussion

Drako3090's avatar
Drako3090
Regular Visitor
9 years ago
Solved

Cummulative Measure for Funnel

Hi everyone, I'm trying to build a measure of cummulative countby a field, to next build a Funnel chart.

 

My data look like this:

 

 

The column [R. Status N°] in the 'Resumen' tab shows the the actual status for an SKU, and to pass to the next one it must have completed all the stages before (like any process with you want to put in a Funnel).

 

I have another table with the status and their names related to this by Status N° ('Funnel' tab):

 

 

Now, I want to build a Funnel chart, but for each stage, I need to count all the SKUs in that stage AND all the SKUs in the next stages (that because, for example, all SKUs in stage 7 have accomplished all the previous stages). I need that the Funnel looks like this:

 

For that purpose, I developed the next Measure:

 

CuentaSKUporStatus = CALCULATE(COUNTA(Resumen[SKU]);FILTER(ALL('Funnel');'Funnel'[Status N°]>=MIN(Funnel[Status N°])))

 

It works almost perfect, but when I put that on the Funnel I got this:

 

 

 

The reason is that for the 5th stage, there is not any SKU in that stage, but I still need that the measure counts all the SKUs in the next stages to look like the first picture. I need an error handling method to help me with this, but I tried many things and no one had worked. Please help me if you can, many thanks from now!

 

Rodrigo V.

  • Vvelarde After I read this, I checked the model and see that I have a Double Directional relationship between the tables. After set it only Single Directional, the measure works as it have to. Many thanks for have replicated the model, if you didn't I would still be crushing my head for the solution.

6 Replies

    • Vvelarde's avatar
      Vvelarde
      Community Champion

      Drako3090

       

      Hi, i reproduce your scenario and your formula and works fine. Maybe i miss something more.

       

      Look

       

      Resumen Table (in my case don't have ETAPA 4)

       

       

      Etapas Table

       

       

      Measure & Funnel

       

       

      Let me know where is the difference in your model.

       

      Victor

      Lima - Peru

      • Drako3090's avatar
        Drako3090
        Regular Visitor

        Vvelarde After I read this, I checked the model and see that I have a Double Directional relationship between the tables. After set it only Single Directional, the measure works as it have to. Many thanks for have replicated the model, if you didn't I would still be crushing my head for the solution.