3 Replies Latest reply on Sep 6, 2016 8:12 AM by David Li

    Measure minus the same measure filtered. Looks easy but is it?

    Federico Armentano

      Hi all! I have a query that at the begining looks easy but I am not sure that it is.

       

      The excel file "Tableau query" contain 2 columns with 4 records each one.

      The packaged workbook "Tableau query" contain an example with the result expected (partially).

       

      The sheet contain all the information. If you filter an ID, for example the ID "D" it change the sheet "Total filtered".

      The sheet "Total no filtered" contain all the information and it does not change with the filter on the detail sheet.

       

      What I would like to calculate is the difference between total filtered and total no filtered, but I do not know how to do it. The result expected is 10.

       

      I would appreciate some help.

       

      Thanks all!

        • 1. Re: Measure minus the same measure filtered. Looks easy but is it?
          David Li

          Hi Federico, you can do this using LOD calculations. Make a calculated field like this:

          SUM([Value]) - SUM({SUM([Value])})

          Then, go ahead and do your filtering, but make sure that you don't use context filters unless you want to also filter from the "unfiltered" set.

           

          EDIT: Sorry, I misread your request at first, so I've updated the formula accordingly.

          • 2. Re: Measure minus the same measure filtered. Looks easy but is it?
            Federico Armentano

            Hi David, thanks for your answers. Your idea is fine and I am sure It really helps others users.

             

            In this case, I forgot to mention something that makes more difficult the situation (actually I did not forget, just I did not realized this problem could happened).

             

            I am working with a primary and a secondary source data. ID is present in both sources, but the filtering y applied from the primary source (since It has more ID). Then, when I moved the calculation "SUM([Value]) - SUM({SUM([Value])})" to rows it say

            "Cannot blend the secondary data source because one or more fields use an unsupported aggregation". It is because vallue es a measure of the secondary data source.

             

            Do you have any idea about how to solve this?

             

            Thanks!

             

            Obs: attached is the tableau and excel file both edited, in order to be more easy to see the situation.

            • 3. Re: Measure minus the same measure filtered. Looks easy but is it?
              David Li

              Hi Federico, that's a bit tougher to solve. If you have multiple data sources, one option you have is to actually duplicate your secondary data source. Make a duplicate of it and then use [ID] from the original one as a filter. Then, you should be able to use a cross-data source calculated field that subtracts the (filtered) original data source's SUM([Value]) from the SUM([Value]) of the unfiltered new duplicate data source.

              1 of 1 people found this helpful