4 Replies Latest reply on Jul 19, 2016 6:41 AM by Michael Hesser

# FIXED LOD to calculate the denominator of sales %

Hi all,

It looks like several Tableau users are and/or have been experiencing similar issues as mine, and unfortunately I haven't found any satisfying solution in the answers provided, hence this post.

I want to display the percentage of sales a specific product category represents every month (as a % of the monthly sales), and I want to display only one category at a time - ideally as a line chart.

In the attached workbook, the first sheet shows the sales by product category as well as the monthly total of sales. The second sheet shows the sales percentage by product category, which is what I'd like to achieve except that I would like to display one line at a time (each pertaining to a product category), on different sheets. If I start using filters, it affects the percentage, and the numbers displayed become irrelevant.

To overcome the issue, what I have tried so far is create a FIXED calculation (it worked pretty well in other circumstances, when I had duplicate values and just needed to use an average) to calculate the total sales per month. I thought that I could then divide each product category values by that fixed calculation... But this led to a new issue (as you can see on the 3rd sheet): instead of returning one line corresponding to the sum of sales each month, Tableau returns three different lines, which follow roughly the same path over time, but differ too significantly from the total I expected to find to be used as a proper denominator in a % calculation.

Thanks in advance for any help.

Thibault

• ###### 1. Re: FIXED LOD to calculate the denominator of sales %

You can use table calculation to filter categories without affecting other calculations:

1 of 1 people found this helpful
• ###### 2. Re: FIXED LOD to calculate the denominator of sales %

Well... that seems to work! Still have to understand how, but thanks a lot! Topic solved.

• ###### 3. Re: FIXED LOD to calculate the denominator of sales %

That's because table calculation takes place after any other. Here is a filters precedence graph from "Understanding Level of Detail Expressions" white paper:

I am sure you can find a complete picture of this topic somewhere - in the community or resources pages - with all operations and not only filters.

Regards

Łukasz

2 of 2 people found this helpful
• ###### 4. Re: FIXED LOD to calculate the denominator of sales %

Thank you for sharing!