7 Replies Latest reply on Nov 17, 2016 11:26 AM by Stephen Rizzo

    Exclude Calculation - but still to show the data

    Tim Bellamy

      Hello,

       

      I am new to posting on the forum and fairly new to Tableau so apologies in advance if this is in the wrong section.

       

      My problem is as follows;

       

      I am trying to create a table where the end user can enter a value to reduce costs by but to also exclude certain categories in the calculation. I have attached a mock workbook that will demonstrate what I am trying to achieve.  I have set up the appropriate calculated fields and parameter to calculate the changes and it works fine when the Exclude filter is added.   The issue I have is that I'm trying to keep the excluded values showing in the table and effectively set the percentage change to zero to show that there will be no change in the adjusted measure.

       

      I hope this makes sense in what I'm trying to achieve but happy to try to clarify further if needed.

       

      Many thanks for any help

        • 1. Re: Exclude Calculation - but still to show the data
          Stephen Rizzo

          So you just want to zero out a specific field for a specific category?

           

          If the categories are guaranteed to be static, then a parameter would be my preferred approach as opposed to the exclude filter. For example, you could create the following calculated field:

           

          [Amount Change (2)] : IF [Type] = [Parameter 1] THEN 0 ELSE [Amount Change] END

          • 2. Re: Exclude Calculation - but still to show the data
            Tim Bellamy

            Thanks Stephen,

             

            I have tried this and it does work in giving me a zero for that specific category, however, the total of that column then becomes zero too rather than adding up the remaining values.  Is there any way to do this? 

             

            I'm a newbie with table calculations and may be setting them wrong but I've tried all of the default options and nothing seems to give me what I want.

             

            Thanks

            Tim

            • 3. Re: Exclude Calculation - but still to show the data
              Stephen Rizzo

              For a more in-depth explanation of what is going on here, have a look at the following article by Jonathan Drummey:

               

              Why Your Grand Total or Subtotal Isn't Working as Expected

               

              The solution that first comes to mind relies on a feature called Level of Detail (LOD) expressions. The key idea is to force Tableau to calculate the "Amount to Change" field earlier in the processing cycle. See the attached workbook for details. I hope that helps!

              1 of 1 people found this helpful
              • 4. Re: Exclude Calculation - but still to show the data
                Tim Bellamy

                That is absolutely perfect in calculating what I expected.  It took me a little while to figure out what needed doing but it appears to work.  The only problem I have now (which is minor) is that I can't seem to get the numbers to right align as they normally would.  I've set them all to right align in the formatting but it must be something to do with the calculated fields?  If there is an answer to this then great, but if not I'm more than happy with the current solution.

                 

                Many many thanks Stephen!

                 

                Tim

                1 of 1 people found this helpful
                • 5. Re: Exclude Calculation - but still to show the data
                  Stephen Rizzo

                  Glad to help! Not sure what you have going on in the formatting - in the workbook I had attached the numerical fields appear to be all right-aligned by default. That should be the case regardless of whether the field is a calculated field or not. If you attach the workbook you are having issues with, I can take a look.

                  • 6. Re: Exclude Calculation - but still to show the data
                    Tim Bellamy

                    Hi,

                     

                    I've attached my workbook again, I think I've done it slightly differently by now using a parameter to choose which category to exclude, I'm not sure if this is causing the formatting issue??

                     

                    What I'd also really like to be able to do is work out the % of the adjusted amount for each category too but I can't seem to use the standard table calculation to achieve this - it just doesn't give me the option.  If you're able to assist with this too then I would be very grateful.

                     

                    Hopefully everything in the workbook is self-explanatory but please let me know if you require further clarification.

                     

                    Thanks again

                    Tim

                    • 7. Re: Exclude Calculation - but still to show the data
                      Stephen Rizzo

                      I think the formatting issue is because of how you constructed the calculated fields. If you take off the FIRST()==0 filter, you'll see a bit more about what is going on. Tableau is trying to put many different marks in each cell, then filter out all but the first. When the software puts multiple marks in a cell, I believe it overrides the normal alignment formatting setting. In other words, if you wanted it aligned differently, you would need to change your calculated fields.

                       

                      Regarding the percentage of adjusted amount varying by [Type], couldn't you use a set of parameters and IF statements in a calculated field? Create a new calculated field that selects a parameter value based on [Type], then swap that in wherever you had your static percentage in your calculations. This is assuming you want it to be adjustable from the dashboard itself (otherwise creating a new field with the percentages in the actual data would probably be preferable).