1 of 1 people found this helpful
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!
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 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!