3 Replies Latest reply on Mar 30, 2018 1:54 PM by Eric Hammond

    Caculated Fields Difference

    mark schukas

      I have a Category Dimension with three different possible outcomes (Furniture, Office Supplies, Technology)

       

      i would like to find the difference between the (Sales of Furniture) - (Sales of Office Supplies)?

      (see screen print below):

       

      //Calc_OnlyFurn

      if [Category] = "Furniture" THEN [Sales] END

       

      //Calc_OnlyOfficeSupplies

      if [Category] = "Office Supplies" THEN [Sales] END

        • 1. Re: Caculated Fields Difference
          Eric Hammond

          Hi Mark,

           

          //Calc_OnlyFurn

          {FIXED: SUM(IF [Category] = 'Furniture' THEN [Sales] END)}

           

          //Calc_OnlyOfficeSupplies

          {FIXED: SUM(IF [Category] = 'Office Supplies' THEN [Sales] END)}

          • 2. Re: Caculated Fields Difference
            mark schukas

            thank you.

             

            I changed and get:

            -seems to be correct for Furniture and Office Supplies, but shouldn't Technology be empty (null)?

             

            ?

             

            thank you.

             

            • 3. Re: Caculated Fields Difference
              Eric Hammond

              Hi Mark,

               

              The level-of-detail calculation, {FIXED: SUM(IF [Category] = 'Furniture' THEN [Sales] END)}, counts all furniture sales and is not limited to detail that supports a given row.  This is why the calc_OnlyFurn column is the same for all three categories.  To demonstrate how that works, compare each category to furniture:

              This looks better if you remove the (Calc_OnlyFurn) from measure values, showing only the sales for each category and the difference vs. Furniture. From here you can repeat the process to add columns to compare each total vs Office Supplies, and vs. Technology, ending up with a grid that compares the departments.  

               

              Another way that Tableau is designed to compare rows against each other is table calculations.  Right-click on the SUM(Sales) pill in the Measure Values Shelf, click quick table calculation, and play around with a few different options.  If any of them fit your need then it saves the effort to set up comparisons.