We have a standard dashboard that shows the key business dimensions of any profitable business; to make more profit, you sell more products, to more people, more often and at higher margin (increased sell price and/or lower cost). One of the graphs I generate to this end is the average number of unique part numbers customers buy, so we can measure our success over time of customers buying a increasing range of products from us.
I have an extract that contains a list of sales transactions, including customer id (number), part number ordered and an invoice date. I'm trying to work out the average number of unique part numbers sold to each buying customer in each year.
I've originally done #unique parts divided by #unique customer ids, but this is clearly wrong; if I did it properly, I should never see less than one unique part number sold a buying customer on average. That said, I can't work out how to get Tableau to spit out the correct aggregation to show the correct average. Any ideas that could get my on my way?
