Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
@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.
@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!
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
Hi @Vvelarde,
I have the same case. I tried your measuer but I received an error message. : Too few arguments were passed to the FILTER function. The minimum argument count for the function is 2.
I have 2 table..
And Etapas:
Funnel:
I need cummulative stages
Hi,
This is going back a little bit but I have recreated the measure used above in my own model to sum a field but then want to click on the funnel sections to filter data in other charts.... when I do this it provides the total sum against all supplier names rather than the sum for that individual supplier. I have tried using SUMX but had no success. Please could you assist?
Thanks.
@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.
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |