1 Reply Latest reply on Jan 31, 2019 1:39 PM by Esther Aller

    Filter Across Multiple Data Sets

    Jason Strate

      Looking for some ideas on how to address a reporting requirement.  We have two data sets that have two common fields, which are date and portfolio.  The first data set has metrics and the second has net asset value.  The report needs to be able to perform aggregations on the metrics in the first data set and then divided by the aggregate of net asset value from the second data set.  Currently the data sets are joined by date.

       

      The trouble the we're encountering is the need to be able to filter by portfolio and have it affect the aggregates on both data sets.

       

      Some of the solutions I've tried which have failed are:

      • Add a filter for each data set, while the behavior is correct, users will not accept this solution
      • Add a link on portfolio between the data sets, but aggregates are then limited to the portfolio, not the total across portfolios
      • Change the filter for either data set to "All Using Related Data Source", but only the data set the filter was create from is affected
      • Add a portfolio parameter, but that limits to a single portfolio since multi-select is not an option
      • Create a measure using TOTAL, which results in a TOTAL (across) results, when TOTAL (down) is required

       

      I'm surprised getting this calculation to work correctly is this difficult.

        • 1. Re: Filter Across Multiple Data Sets
          Esther Aller

          Hi Jason,

           

          It sounds like you may be using data blending to combine the data from the two data sources into one view. Typically I recommend doing a table join as data blending has many limits. If the two data sources are in different databases, then a cross-database join will work. For directions, please see the following entry of the online Product Help guide Join Your Data - Tableau

           

          If you stay with data blending and using TOTAL(), then the way the total is computed can be changed. The simplest way is to right-click the field in the view and select Compute using > Table (down). I really like how this entry of the online Product Help guide explains more advanced table calculation options: Transform Values with Table Calculations - Tableau

           

          Hope this answers your question!