I have data on 3 separate tables in 3 separate worksheets that I need to sum together. There are reasons I can't put them all on 1 worksheet, due to the way the data is formatted and my need to filter it different ways to get different solutions from the same data points.
So on Worksheet 1 I have Table A Row 3
So on Worksheet 2 I have Table B Row 3
So on Worksheet 3 I have Table C Row 3
I'm looking to create one more worksheet where I can sum the three Row 3s on the different worksheets, but I don't see any way to reference a worksheet in a calculated field. (i.e., in Excel you can specify a worksheet and cell in a formula. In Tableau you can only reference a cell, it seems).
Any idea if there is a way to sum values from tables across multiple worksheets?
Thanks!
Dave
David,
If you create a new calculated field (I called it "Shadow Total") with the following formula, I think you will have the total you want.
zn(sum(if [Market Type Adjusted] = "Day Ahead Energy" then [DA Award - MW] else 0 end)
*avg([Custom SQL Query (eto1p)].[DA_PRICE]))
+
zn(avg(if [Market Type Adjusted] = "DA Reg Down - Capacity" then [DA Award - MW] else NULL end)
*SUM([Custom SQL Query (eto1p)].[REG_DOWN_DA_PRICE]))
+
zn(avg(if [Market Type Adjusted] = "DA Reg Up - Capacity" then [DA Award - MW] else NULL end)
*SUM([Custom SQL Query (eto1p)].[REG_UP_DA_PRICE]))
The caveat with this is that it is hard coded to the 3 calculations that are in row 3. If you want the dashboard to be useable for other groupings of measures, you should be able to create a parameter and have if or case statements to adjust for the parameters.
