Funnel and subset of data

[Jonathan_P]

Hello,

I have a fact table with a Status dimension. The status (here, called "Nom" in french for "Name") is the "stage" where a credit application is. So a credit application that is at status Id 4 passed through all the other statuses and must be counted on each total. In other words, each set is a subset of the other above (except the first).

I want to use a funnel visualization showing that.

What is the best way to achieve that?

I am thinking about adding a measure to do a running total of the total column shown here.

Thank you

[Bokazoit]

My thought exactly. Running Total 🙂

[Jonathan_P]

@Bokazoit,

I tried to do a running total instead of doing it using queries as I explained above but I am struggling.

The problem is when there is no data for the dimension.

For 'Véhicule soumis', there is no data (i.e. Total = 0). In this case, the 'Running Total' returned is the grand total of 35 instead of 14.

The measures are:

Total = IF(ISBLANK(COUNTROWS(FactCreditApplications)); 0; COUNTROWS(FactCreditApplications))
Running Total = CALCULATE([Total]; FILTER(ALLSELECTED(FactCreditApplications); [StatusId] >= MAX([StatusId])))

What I am doing wrong?

Thank you

[Jonathan_P · Accepted solution]

As an update on this thread, I found a solution for my problem. Finally, I use DAX expressions and measures (and nothing about it at queries level).

Here are my 2 measures:

Count = 
	IF (
		ISBLANK ( COUNTROWS ( FactCreditApplications ) );
		0;
		COUNTROWS ( FactCreditApplications )
	)

Total = 
	CALCULATE ( [Count]; FILTER ( ALL ( DimStatus ); DimStatus[StatusId] >= MAX ( DimStatus[StatusId] ) ) )  

[Jonathan_P]

I solved it using the queries.

My fact table now contains every records needed by the Funnel visualization to get the desired result. I use now the Group bucket (before I had a measure for each stage and wasn't using the Group) and I dropped the stage name from the new stage dimension in it. So the same credit application may appear multiple times but related to a different stage. This is a different fact.

To achieve that:

-I have a base query with all transformations (called 'FactCreditApplications_Base') containing a column returning what I call at that level the status id.

-I have a few queries, one per stage (by example, 'FactCreditApplications_Approved') that make use of the Reference feature. They are based on the query above. Each one filter on the status column where the id must be greater than or equals to the according status id. Each queries adds also a custom column returning the appropriate stage id.

-All the queries above aren't loaded (i.e. 'Enabled Load' unchecked).

-Another query that basically just append all the others (called simply 'FactCreditApplications') using the Append Queries feature. It uses the Power Query command Table.Combine.

-I have a dimension table called DimStages.

I think this is the good design for that kind of need and the key is the use of the Reference and the Append Queries features.

Your thoughts?