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

    Chirag Shah

      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:

       

      Tableau Submit 1.PNG.png

       

      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:

       

      Tableau Submit 2.PNG.png

      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],

      IIF([Customer].[Country].lead(2) = [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.

       

      Use the MDX lag function instead of lead where needed.

       

      Which gives us the crosstab pictured above.

       

      For the bottom phase the calculation is of the form:

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

      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.