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

    Incorrect Subtotals for calculation using the Fixed expression

    Progenthusiast

      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.

       

      Thanks a lot in advance!