How about the attached?
I duplicated the data, with custom SQL like:
SELECT [Sheet1$].[Created Date] AS [Created Date], [Sheet1$].[Member Age] AS [Member Age], [Sheet1$].[Member Gender] AS [Member Gender], 1 AS [Pane] FROM [Sheet1$] UNION ALL SELECT [Sheet1$].[Created Date] AS [Created Date], [Sheet1$].[Member Age] AS [Member Age], [Sheet1$].[Member Gender] AS [Member Gender], 2 AS [Pane] FROM [Sheet1$]
Notice that I added a field. This enables another subtotal column, and I can hide one, and the display looks as requested. Then with a calculated field like:
IF MIN([Month])=MAX([Month]) AND MIN([Pane])=MAX([Pane]) THEN SUM(1) ELSEIF MIN([Pane])=MAX([Pane]) THEN SUM(1)/COUNTD([Month]) ELSE SUM(1)/2 END
That will give you the normal sum under the month headers, and the average for the "Pane" subtotal, and the sum divided by 2 to reverse the effects of the duplication of the data.
totalagerage_jm_edit.twbx 109.4 KB
Can you add the average as an additional column rather than an additonal row without modifying the underlying data (i.e., without changing the base table(s), without creating a view/stored procedure, etc.)?
Scott, I am not exactly sure what you are asking, but I will try to address your question as I understand it.
What you can do in Tableau is directly related to your data source structure. Different data source structures allow Tableau to create different views.
Tableau can do many great things by itself, and without transforming data. Although, in order to generate the view requested, I transformed the data source to enable Tableau to create the view.
I do not believe it is currently possible to create the view without preparing the data source for Tableau.
Yeah, I think we are on the same page. I'm just looking to add a column, using Tableau, called Average.
So, in other words, we'll have to change the source data in order to add an additional column that's not an "automatic" calculation (for lack of a better term) such as Total, Grand Total, etc. Is that correct?
Please let me know if the above doesn't make sense and I'll provide an example of source data and the expected output.
Thanks very much for your prompt reply. It's very much appreciated!
Since any of terms here are overloaded with multiple definitions, it is always best to provide an example of source data and the expected output.
No problem, Joe. Here you go. Unfortunately, due to the whitespace being trimmed when pasting in it's not too pretty. Please let me know if you have any questions. Thanks very much. Scott
Sample Input Data (There are 5 rows of data and 2 columns, [Order Date] and [Cost]):
[Order Date] [Cost]
Expected Tableau Output:
Jan-2010 Total Cost Feb-2010 Total Cost 2010 Total Cost 2010 Average Cost
----------------------------- ----------------------------- ----------------------- ---------------------------
900 600 1500 300
Expected Tableau Output (Calculations):
2010 Total Cost = Sum([Cost]) Where Year([Order Date]) = 2010
200 + 300 + 400 + 100 + 500 = 1500
2010 Average Cost = Sum([Cost]) / # of Records Where Year([Order Date]) = 2010
1500 / 5 = 300
I am unable to see how your situation is different from Alex's. Maybe with more data the difference in your situation would be more apparent.
Attached is made using your provided data, and the same method as above.
Here is the modified calc that I used:
IF MIN([Month])=MAX([Month]) AND MIN([Pane])=MAX([Pane]) THEN SUM([Cost]) ELSEIF MIN([Pane])=MAX([Pane]) THEN SUM([Cost]) ELSE AVG([Cost]) END
order_data_cost.twbx 12.8 KB
Scotts' issue is exactly the same as mine. And your solution is definitely works. But the problem that this doubles the data. And if users export (view) data they will think it's twice as many rows.
Is there any ways of solving this without doubling rows? Is there way use Year twice as Column, but somehow include depth of subtotals to differentiate Totals vs. Averages? Of something using Attr() function - I tried many things but always came short of actual solution.
When roll mouse cursor over the values it shows "Pane: All" on Totals vs. "Pane: 1" on Averages. But I was unable to use this within calculations.
Any advice -including "this can’t be done" is very appreciated.
Thank you, Alex
I don't believe Tableau intended there to be two subtotal columns for one pane like I have created here. I am adjusting the visual display by manipulating the underlying data, and I do not know of another way to accomplish what you are asking for. Duplicating the data is what enables this view.
As for a user that wants to view the data, you could create another worksheet that only has the non-duplicated data, or a worksheet with a filter that removed one of the "Pane" values, so other global filters work, or use actions to connect the sheets and filter across data sources. I know that is not ideal, but you could have one sheet for viewing, and one sheet for exporting or viewing the underlying data.
Another option is to have a single Total/Average column that is controlled by a parameter, so the use can select what is displayed in the single subtotal column.
As a side-note, I wanted to thank you very much for all your postings. Without your hard work, great posts and time spent helping everyone I wouldn't be able to complete nearly *any* of what I've been tasked.
Thanks so very much.
I have a situation that somewhat follows the issue described in this thread. However, I'd like to know if there's a way to have a row of averages.
I've attached a workbook showing total scores for various test types and test companies. I created an average score column, maximum, minimum, and range.
Is there a way to have a row that calculates the averages of these columns and lists those averages across the bottom, similar to the grand total?
Essentially, show the average of average total scores, average maximum, average minimum, and average range.
row_of_averages.twbx 23.4 KB
Just as an FYI, the way we ended up accomplishing the above is to calculate all of the values on the back-end and merely display/format the results in Tableau. The data behind the scenes ("view data") ends up being the data as it will be displayed on the report. If you want to display this on a Dashboard with multiple worksheets, that would be your best bet although I'm sure that Joe has a few more tricks up his sleeve than I do :-)
Thank you for the info. I'm not sure if calculating the values on the back-end will work for this situation, but what do I know?!
I'm just having difficulty figuring out how to calculate the average of each column, as well as having these averages display along the bottom.