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

    How to perform a distinct count of a cube dimension?




      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!