1 Reply Latest reply on Apr 17, 2016 11:41 AM by Yuriy Fal

    Formula across two data sources

    Liron Chakim

      Hi,

       

      I'm trying to create a formula with an effect similar to "SUMPRODUCT" function in excel (a weighted average, basically).

       

      However the fields are from two data sources.

      The primary datasource (which is an SQL query) holds a measure called "Products_sold", and the secondary (which is an excel file) holds a measure called "Price".

       

      When I tried this:

      SUM([Secondary_datasource].[Price])*[Products_sold])/SUM([Products_sold])

       

      I get the following error message:

      "All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources".

       

      So I tried adding "sum" as I saw some suggestions to do that:

      SUM(SUM([Secondary_datasource].[Price])*[Products_sold])/SUM([Products_sold])

       

      and then I received this error message:

      "cannot mix aggregate and non-aggregate arguments with this function"

       

      So I thought of maybe adding "sum" to the primary data source field:

      SUM(SUM([Secondary_datasource].[Price])*SUM([Products_sold]))/SUM([Products_sold])

       

      and then I received this error message:

      "Arguments to SUM (an aggregate function) is already an aggregation, and cannot be further aggregated"

       

      So I'm out of ideas, seems every step I try is resisted by Tableau.

       

      Any solutions?

        • 1. Re: Formula across two data sources
          Yuriy Fal

          Hi Liron,

           

          You may try to use this Table Calculation:

           

          MIN([Secondary_datasource].[Price]) * SUM([Products_sold]) / TOTAL( SUM([Products_sold]) )

           

          You should have your blended field(s) in a view (in Details, Rows or Columns),

          make sure these fields (Dimensions) to come from a Primary datasource.

           

          The Table Calculation above is set to Compute using your blended fields.

           

          Hope this could help.

          If not, please share a sample workbook with your data and a desired output.

           

          Yours,

          Yuri