2 Replies Latest reply on Sep 12, 2019 6:52 AM by Samik Pal

    Add to context Calculation_SecondaryDataSource

    Samik Pal

      Hello Experts,

       

      This is somewhat a simplified version of the sample data source I am currently working with.

       

       

      BUPeriodActuals
      10111000
      10122000
      10133000
      10144000
      102110000
      10222000
      102330000
      102330000
      102440000

      Actual Table

       

       

      BUNameSQF
      101SSCP1024
      102Gateway2025

      BU Table

       

      The BU Table has 1 to many relationship to Actual Table

       

       

      My requirement is to be able to present the data through a matrix viz and present it with a Period filter.

       

      When the user selects the filter from the filter pane, the BI tool should return a calculated column such as following

       

      ACT/SQF_Annualized = ((Sum of Actuals for the selected periods/ Total SQF)/(Total count of periods selected from the filter))*12

       

       

       

       

      I wanted to replicate this view in tableau and failed. I tried follow this post - Count selected values in a drop down filter .

       

      The post mentions two tricks-

      A. add to context,

      B. create LOD

       

      The tricks work perfectly when the field 'add to context' comes from Primary Data source but if it comes from (in my example) secondary data source it does not even give 'add to context' option for that.

       

       

      My limitation is -

      A. I can't make my secondary data source as my primary data source to cater to this particular requirement as it would effect other calculations that have already been performed.

      B. Currently I am blending data and I can't make joins with my primary and secondary data source which would affect existing calculation.

       

      It is currently executable in Power BI as views above by sticking to the power bi version of data blending, Can tableau return this view?

       

      Thank you in advance.

        • 1. Re: Add to context Calculation_SecondaryDataSource
          Ken Flerlage

          Instead of blending, I'd recommend you join your data like this:

          This will duplicate your SQF value, so we'll use a FIXED LOD to address this problem:

           

          SQF Adjusted

          // Use a FIXED LOD to eliminate impact of the record duplication.
          {FIXED [BU]: MAX([SQF])}
          

           

          Then you can calculate your annualized value as follows:

           

          Annualized

          // Annualized...
          ((SUM([Actuals])/SUM([SQF Adjusted]))/ATTR({FIXED : COUNTD([Period])}))*12
          

           

          Because we're using an LOD to get the total number of periods selected, we'll want the Period filter to apply before the LOD (otherwise, it will just always return the total number). So, right-click on that filter and add it to context. For more, please see the Tableau's Order of Operations - Tableau

           

          Then build this view:

          See attached.

          • 2. Re: Add to context Calculation_SecondaryDataSource
            Samik Pal

            Hello ,

             

            Thank you very much for taking time out to look into this. In reality, I have 4 data sources which are all blended to create a matirx viz for the client already. It contains a a lot of calculations already.

            It would be a lot harder for me to create a join and to recreate the calculations.

             

            is there any way for tableau to return what I have in mind from existing blending and without altering the existing data source relationship?