when we blend two data source and use parameters means we can use same type of aggregation in both fields.
like here. i have sample super store and coffee chain dbs.
I want to take sales from coffee and remaining from super store excel.
When we take sales from coffee chain it come as
"SUM([CoffeeChain Query (Sample - Coffee Chain)].[Sales])"
when we take profit from super store then it comes as
[Profit], it missed sum here so we can use both with sum
case [Measure Parameter]
when "Sales" then SUM([CoffeeChain Query (Sample - Coffee Chain)].[Sales])
when "Profit" then sum([Profit])
When we use dimensions
case [Dim Parameter]
when "Customer Segment" then attr([Customer Segment])
when "state" then ATTR([CoffeeChain Query (Sample - Coffee Chain)].[State])
both should be attr
Please attach sample workbook.
Thanks @sankar.rajan, appreciate the help. It's your second example of using a dimension I'm trying to achieve. I've attached a sample workbook with two sheets. The first uses a calculated field with no aggregation - Chart drilldown (primary source only):
CASE [Chart Parameter]
WHEN "Continent" THEN [Continent]
WHEN "Department" THEN [Department]
This works fine to display sales by Continent and Department.
The second sheet attempts to extend this to display other attributes of a continent from a secondary source, which requires aggregation of all dimensions, see the calculated field Chart drilldown (aggregation):
CASE [Chart Parameter]
WHEN "Continent" THEN ATTR([Continent])
WHEN "Department" THEN ATTR([Department])
WHEN "Drought" THEN ATTR([Sheet1 (continent info)].[Drought])
WHEN "OECD ranking" THEN ATTR([Sheet1 (continent info)].[OECD ranking])
This plots all charts as a single line, ie aggregation of all continents or departments.
drilldown sample.twbx 1,005.5 KB
1 of 1 people found this helpful
When we do this calc in separate then it works fine.
when we blend two calc then it will show in measure place and it will not moved from here to dim.
Mark Fraser please check this.
Thanks Sankar. Will see what @mark.fraser.0 has to say but I think you're suggesting the problem is that I've ended up with a measure rather than a dimension. And I understand that's because the calculation includes an aggregation, which I require because of the secondary data source.
I've no doubt there are other ways to achieve this, such as combining the data at the source, or a worksheet for each chart view I want and some dashboard parameter to dynamically choose the sheet. I'm hoping for something fairly simple though!
Also, please let me know if there's any feature in a more recent version of Tableau (I'm using 8.2) that would help.
Just a followup for others, I tried v9.0 and the same problem. I think rather than dynamically choosing a different sheet I'll try to put the secondary data into the primary source. Fiddly and annoying at that end, but the Tableau work will be simpler.