5 Replies Latest reply on Aug 5, 2018 11:13 PM by KANTHESH M.D

# Incorrect Subtotals for calculation using the Fixed expression

I am using LOD calculations in Tableau. While my expression is giving the right result for the percent displayed within a cell in a row, the subtotals are not correct as they exceed a 100% and are the cumulative sum of the percentages. How can I get to display the correct subtotals while using a "fixed" expression for my calculation?

I am using the "sample superstore" Tableau workbook as an example for my question. I created a parameter, "Test", which switches between the Dollar amount and percent. I created a calculated field, "calculation1" as well and used the following:

``CASE [Test]  WHEN 'Dollars' THEN { FIXED [Order Date], [Category], [Sub-Category]: SUM([Sales]) }  WHEN 'Percent' THEN -{ FIXED ([Order Date]), [Category], [Sub-Category]: SUM([Sales])} / { FIXED YEAR([Order Date]), [Category]: SUM([Sales])}  END ``

The negation is for the custom formatting to display percents. I am getting 300% as the grand total when I switch to percents. If I use more dimensions in the column using parameters for switching columns, I would use more dimensions in the "fixed" expression. How should I display the correct values for percentage for subtotals and totals.

• ###### 1. Re: Incorrect Subtotals for calculation using the Fixed expression

What Happens when you keep cursor at Subtotal, Is the Menu Displays Automatic? If It is, Change it to SUM. If That Does n't Work,please attach a workbook.

Thanks

Deepak

• ###### 2. Re: Incorrect Subtotals for calculation using the Fixed expression

I tried it. SUM is the default. It is still displaying the cumulative percent. I have attached the Tableau example. The subtotals get displayed as 300% and the grand total is 1200% which are incorrect. Thanks a lot again!

• ###### 3. Re: Incorrect Subtotals for calculation using the Fixed expression

Pl Check attached. When Using Fixed since you are aggregating Each Category in a year, so You are likely to get it that way. I changed your Calc to Table Calculation to get it.

Thanks

Deepak

• ###### 4. Re: Incorrect Subtotals for calculation using the Fixed expression

I used a Table Calculation earlier and it is good when I don't use calculated dimensions and parameters to swap the columns. When I do so, all the dimension pairs don't display the correct percent using a Table Calculation as I can compute the percent based on only one dimension, so I would need a fixed expression to compute the percents for different dimension pairs. Would there a way a to display the correct percents when using the fixed expression?

Thanks!

• ###### 5. Re: Incorrect Subtotals for calculation using the Fixed expression

Hey Hi,