cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Drako3090 Frequent Visitor
Frequent Visitor

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:

 

Data.png

 

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):

 

Data Status.png

 

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:

 

Funnel.png

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:

 

 

Funnel2.png

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Drako3090 Frequent Visitor
Frequent Visitor

Re: Cummulative Measure for Funnel

@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 6
Drako3090 Frequent Visitor
Frequent Visitor

Re: Cummulative Measure for Funnel

@rdurkin 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!

Vvelarde Super Contributor
Super Contributor

Re: Cummulative Measure for Funnel

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

 

Resumen.png

 

Etapas Table

 

Etapas.png

 

Measure & Funnel

 

Measure & Funnesl.png

 

Let me know where is the difference in your model.

 

Victor

Lima - Peru




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Drako3090 Frequent Visitor
Frequent Visitor

Re: Cummulative Measure for Funnel

@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.

dixon1983 Frequent Visitor
Frequent Visitor

Re: Cummulative Measure for Funnel

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.

slavo Frequent Visitor
Frequent Visitor

Re: Cummulative Measure for Funnel

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 

Vvelarde Super Contributor
Super Contributor

Re: Cummulative Measure for Funnel

Hi. You miss a ) in All(table1)



Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 39 members 985 guests
Please welcome our newest community members: