4 Replies Latest reply on Jan 2, 2019 10:20 PM by pavan hm

    Include all Measure Names with missing data (blended data)

    Jordan Laughlin

      I am looking at budget versus actuals financial data, with my budget in one spreadsheet, and actuals in another spreadsheet.

       

      The two data sources are blended based on a cost codes (e.g. 'Travel', 'Legal', 'Finance', etc.), and I am looking for a side-by-side bar chart broken out by the cost code.

       

      However, for projects where there is no data under certain cost codes, I would still like them to show in the chart. Is this possible?

       

      Please see the attached image for what I have so far, and note that there are about 6 missing cost codes where there is no data. (I apologize for not attaching a twbx, there is a lot of confidential data mixed in.

       

      budgetactual.PNG

        • 1. Re: Include all Measure Names with missing data (blended data)
          Yuriy Fal

          Hi Jordan,

           

          You may want to blend both Actual and Budget datasources

          to a third 'Scaffold' datasource (used as a Primary on a view).

           

          The Scaffold datasource should be 'domain-complete',

          ie it should contain all Dimensions values combinations --

          even those that are absent in either Actual or Budget (or both).

           

          Please include all the Dimensions needed for the view into the Scaffold --

          especially those to be used on Filters.  As you can see on your view,

          the Filters from the Secondary datasource are restricting the scope.

           

          Hope it could help a bit.

           

          Yours,

          Yuri

          • 2. Re: Include all Measure Names with missing data (blended data)
            Jordan Laughlin

            Hi Yuri -

             

            Thanks for the response. From my knowledge, the only two ways I could accomplish your suggestion is to either:

             

            1) blend the two data sources at the source level (whether in Excel or another tool) before importing to Tableau, or

             

            2) join the two sources together in the Tableau 'Data Source' section.

             

            Am I correct, or is there another way to get a third blended data source that can be used as the Primary on a view?

            • 3. Re: Include all Measure Names with missing data (blended data)
              Yuriy Fal

              Hi Jordan,

               

              The 'third' datasource (i called it a 'Scaffold') could be made in Excel.

              In a nutshell, it would include all the Dimensions that are on a view

              (including those used as Filters) -- in your particular case they are

              the [Cost Category] and the [Project].

               

              To build a Scaffold datasource file, you can create two Excel sheets --

              the Cost Category and the Project -- with just one respective column in each.

               

              The columns should contain all possible unique values that you may have

              in your data -- so they can be described as 'domain-complete'.

               

              In Tableau, use a cross-join of these two sheets ( on 1=1 Join Calculation, for example)

              to have a datasource that contains all possible combinations of the two columns values.

               

              Then use this Scaffold datasource as a Primary, and blend both your datasources to it

              on the common column(s). Use a MIN() aggregation for Measures from the datasource

              with a lesser granularity (sans Projects), and use a Table calculation WINDOW_SUM()

              to have the correct figures when blending data from the datasources like that.

               

              If the above sounds complicated, please come up with the sample data,

              i'll try to make a Scaffold and a Blend.

               

              Yours,

              Yuri

              • 4. Re: Include all Measure Names with missing data (blended data)
                pavan hm

                Hi Jordan,

                 

                Instead of blending you can join both the spreadsheets. If you could share the twbx with some dummy data with the same structure , i can have a look at it .

                 

                Thanks,

                Pavan