1 Reply Latest reply on Oct 25, 2013 3:43 PM by Tracy Rodgers

# Determined how to create a funnel chart when using Microsoft Analysis Services as a data source

Hello fellow Tableau users,

I had worked for about 5 business days to create a funnel chart using a cube (Microsoft Analysis Services) as a data source.

My goal was to create a chart that looked like this:

I used the excellent video at Funnel Charts | Tableau Software as a guide, but I couldn't figure out how to create the equivalent of

IF ATTR([Phase])="Website Hit" OR LOOKUP(ATTR([Phase]),-1)="Website Hit" Then Sum([Number of Prospects])

in MDX land.  After all, dimensions are not accessible in Tableau when writing calculated fields when connected to SSAS (SQL Server Analysis Services).

I finally found the trick to make this work.

Suppose we want to visualize customer count by country in a funnel chart using the cube “Mined Customers” in the Adventure Works DW 2008R2 cube found at Microsoft SQL Server Product Samples: Database - Download: AdventureWorks 2008R2 Analysis Services Project.

You want to shape the data such that each row is populated with exactly 4 values with the exception of the top row:

The necessary pattern for the funnel chart to work is: Row X must intersect with the Calculated Measure for that country and the Calculated Measure for the country that comes before Row X when sorted by Customer Count, descending.

Create MDX calculations as follows:

For all phases except the bottom phase, create calculations of the form:

IIF([Customer].[Country].currentmember = [Customer].[Country].&[United Kingdom],[Measures].[Customer Count],

NULL))

Let's break this apart.  "Country" is already on the row shelf.  The MDX expression states that "if this is the United Kingdom row, or if the row 2 after the current row is the United Kingdom (alphabetically), this cell should have a value equal to the customer count in the United Kingdom.

Which equates to "the calculated measure 'CM - United Kingdom' intersects with United Kingdom and France.

Which gives us the crosstab pictured above.

For the bottom phase the calculation is of the form:

NULL)

Follow the rest of the video to finish the chart.  You can put negative values (as directed in the video) before your measure in this fashion:

IIF([Customer].[Country].currentmember = [Customer].[Country].&[France],-[Measures].[Customer Count],

IIF([Customer].[Country].prevmember = [Customer].[Country].&[France],-[Measures].[Customer Count],

NULL))

That's it!  Hope this helps someone in the near future.

• ###### 1. Re: Determined how to create a funnel chart when using Microsoft Analysis Services as a data source

Awesome, Chirag! Thank you for sharing this--cubes are a different animal and can be a little more complicated to work with!