Forum Discussion
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
- Drako3090Regular Visitor
Anonymous can you help me with this? I saw this topic from you that is similar but not equal:
http://community.powerbi.com/t5/Desktop/Cumulative-Running-Totals-in-DAX/m-p/42608
Thanks!
- VvelardeCommunity Champion
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
- Drako3090Regular 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.