4 Replies Latest reply on Jun 8, 2016 9:37 AM by Joshua Milligan

    How to perform a distinct count of a cube dimension?

    adam.medcalf

      Hi,

       

      I’m relatively new to Tableau and have a question about creating calculations in Tableau when using cubes, as I know from an article on the website that some of the features are different/not there with cubes. My cube is a Microsoft Powerpivot one hosted on Sharepoint, if it makes  a difference.

       

      Imagine a table of sales, 1 row per sale, with fields like customer ID, date and # products purchased.

       

      I would like to make a calculation in Tableau of average products sold per customer, with a view to making a chart showing change in products sold per customer. The calculation I therefore need is:

       

      Sum of product purchased / count distinct of customer IDs
      over a period.

       

      I know the sum and countd function but can’t understand how to make it work in terms of the cube data in Tableau.

      1) If I go to “create calculated field” then in my “fields” list I have only the measures listed, and not the dimensions. Customer ID is a dimension so I can’t do a countd of that in my calculated field it seems.

       

      2) If I right click customer ID on my dimensions list I can choose create measure, and it generates me a “Count of customer ID” measure, but this is a count and not a distinct count. I can right-click -> edit that new measure to change the aggregation to sum, average, minimum, maximum but there is no distinct count as an option.

       

      3) Because it is cube data I cannot create a local Tableau data extract and play with it there.

       

      I guess this is a common requirement so I apologise if I miss something obvious. Any hints appreciated!

       

      I saw in a file at http://downloads.tableausoftware.com/quickstart/feature-guides/calcs.pdf

      that for “advanced calculations” I can use MDX. I imagine the above isn’t really "advanced", and in any case I don’t know how to reference fields in MDX…it looks quite complicated for mere business users to learn in a hurry :-)

       

      Thanks for any help!

       

      Adam