Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Drako3090
Regular 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

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

View solution in original post

6 REPLIES 6
Drako3090
Regular 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!

@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

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
Community Champion
Community Champion

Hi. You miss a ) in All(table1)



Lima - Peru

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.