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.
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?
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.
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 .