5 Replies Latest reply on Sep 2, 2016 1:21 PM by Andrew Watson

# Distribution Calculation Based On Product Category/Year

Hello all!

I've been lurking in the forums and have found an answer to almost every questions I've had with Tableau, but ran across a snag.

Here is what I am trying to achieve.......

• I'd like to calculate the distribution percentage on a specific product on that given year.
• Multiple years are displayed
• Example (kind of using Super Store Data):
• In 2014, 100 Customers purchased a copy machine
• Of those 100 customers, 80 purchased model #1a, giving that product an 80% distribution rate in the "Copy Machine" product category
• In 2015, 125 Customers purchased a copy machine
• Of those 125 customers, 12 purchased model #3b, giving that product a 9.60% distribution rate in the "Copy Machine" product category
• In 2016, 90 Customers purchased a copy machine
• Of those 90 customers, 33 purchased model #2C, giving that product a 36.67% distribution rate in the "Copy Machine" product category

I've been racking my brain trying to figure out how to write the "IF [Product Category] CONTAINS [PRODUCT] THEN TOTAL(SUM(COUNTD([CUSTOMERS]))" for just that specific category that the product falls into (I know that formula is WAY off, but I think that is the best way to illustrate what I am trying to get at).

Making maters worse, in a best case scenario I'd like to have multiple product categories shown on one suppliers page - IE if the company selling above also sells paper, I'd like to show the distribution percentage of those items to stores that purchased only "Office Supplies" and not both "Copy Machines" and "Office Supplies."

Thank you in advance for any help that you can provide!

Mat

• ###### 1. Re: Distribution Calculation Based On Product Category/Year

Sounds like you should be looking at LOD calculations. Check this out, very useful: Top 15 LOD Expressions | Tableau Software

As an example you could have (based on your example): {FIXED [Product Category], YEAR([OrderDate]): COUNTD([CustomerId])}. That will give you a fixed number of the total customers for each product category for each year. Effectively the denominator for your example calculations.

You can also try {INCLUDE [Product Category], YEAR([OrderDate]): COUNTD([CustomerId])}, which operates slightly differently then FIXED, especially when it comes to filters.

Note I haven't checked the field names of Superstore so please alter as necessary. If using Tableau 9 you'll have to put the YEAR([OrderDate]) into a seperate calculated field and call that calculated field instead of referencing the YEAR function.

1 of 1 people found this helpful
• ###### 2. Re: Distribution Calculation Based On Product Category/Year

Andrew,

Thank you! I'm almost there, but after using your example I came across another obstacle. I need it to only count customers within a customer category - IE Offices/Warehouses/Homes/ETC. Even when I apply filters it is calculating *all* customers that purchased from that product category, not just the one that I have filtered down to.

Mat

• ###### 3. Re: Distribution Calculation Based On Product Category/Year

You can put your filters into Context - that should solve it if you're using FIXED.

Alternatively see if the INCLUDE formula works and please post back which you ended up going with

1 of 1 people found this helpful
• ###### 4. Re: Distribution Calculation Based On Product Category/Year

Context is what needed to happen. Now everything is working properly. THANK YOU SO MUCH!!!

• ###### 5. Re: Distribution Calculation Based On Product Category/Year

Great, glad it's working for you and thanks for feeding back. Can you please mark one of the answers as correct to close out the thread? Thanks

1 of 1 people found this helpful