2 Replies Latest reply on Sep 19, 2016 10:35 AM by Joshua Milligan

    Count Dimension Table Ignoring Fact Table

    Rohan Laas

      I want to count the total amount of customers and divide that number with the number of customers in the table with transactions.

       

      I joined my customers table to my sales table and counted the customers in the sales table which works. Now I want to divide that number by the total number of customers in the customer table regardless of whether they are in the sales table.

       

      I am using this measure: {FIXED : COUNTD([CUST CD (Customer MasterData)])} to count the customers in the customers table but it is only taking into account the ones joined to the sales table.

        • 1. Re: Count Dimension Table Ignoring Fact Table
          Joshua Milligan

          Rohan,

           

          I think this is exactly what you'd expect since your data source defines the connection as an inner join between the fact and the dimension.  You'll only ever get records for customers with sales.  There's no way around this given the definition of data source (LoD doesn't change that).

           

          But, if you were to create a second connection to only the customer table, then you'd have a record for every customer from that source.  And then you can use your existing Sales source as the primary data source (to get customers with sales) and the secondary customer table source to get the total number of customers.

           

          With those two connections, in the Sales connection you can create a calculated field that does the division (code will be something like):

           

          COUNTD([Customer ID]) / COUNTD([Custeroms.Customer ID])

           

          If all you want is the total number of customers from the customer table, then make sure you turn off all linking fields in the blend (and ignore Tableau's warning that there aren't any relationships).

           

          Hope that helps!

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Count Dimension Table Ignoring Fact Table
            Joshua Milligan

            Rohan,

             

            Another option (especially since blending sometimes gets difficult with count distincts -- the second count distinct above could probably just be a count to get around this because I assume your dimension has one record per customer), if you really want a single data source, is to use Custom SQL to get a count from the Customer table as a subquery.  You'd do something like this:

            1. Edit the Sales conneciton you have, use the menu to select Data > Convert to Custom SQL

            2. Edit the Custom SQL object to add:

             

            SELECT

                 [field 1],

                 [field 2],

                 (SELECT COUNT(*) FROM Dim_Customer) AS Total_Customers,

                 ...

            FROM Fact_Sales s

            INNER JOIN Dim_Customer c on c.[Customer ID] = s.[Customer ID]

             

            That'll give you the total customers per row of data, so you'll want to make sure to account for that in any visualizations and calculations (e.g. don't SUM the value for every row!)

             

            So, the calculation you want to do would be something like:

            COUNTD([Customer ID] / MIN(Total_Customers)

             

            Hope that helps!

            Joshua