There are ways to accomplish this in Tableau. My suggestion would be to create a workbook using sample data, mock up what you want a final result to look like, and challenge the forum members to create a solution that matches your mock up. By looking at this, I'm not really sure what you expect the final view to look like, and questions are much easier to address when a packaged workbook (.twbx file) is in play--you are also much more likely to get a quality response with a workbook attached.
Cheers! The good news is: there is always a way.
as per Matthew suggestion, I am attaching a Tableau workbook to illustrate the point I need to solve
here is the link Workbook: changing calc at diff levels of data
in this workbook, the 'raw level view' and the 'child level view' are ok.
but the calculation of the 'father level view' needs to sum instead of avg the child level items
as in a master-detail relationship.
how to achieve this in tableau, without having the child level dimension (child) in display ?
thank you for any help.
Here you go. The formula used is:
IF FIRST()=0 then WINDOW_SUM(avg(hours)) end
The IF FIRST()=0 is necessary, as without it, 192 is displayed twice for Father-25. (Someone else can probably do a better job explaining how this stuff works, its still very much about experimenting for me)
With "compute using" set to Child, and Child on the level of detail in the view (so it is not displayed in the view, but can be used in the Table Calc)
is works ok
the only thing is it mess up the format of the number, but I guess is not difficult to correct
If you mean it has one decimal place and not two, that is easy to fix (see attached).
If you mean the layout and where this number ends up being displayed, I am not sure how to move it around. I believe there are ways to get it right aligned, but I'm not sure what is required to do that. Perhaps Jonathan Drummey can comment on more options; I know I've seen the layout addressed before, but I cannot recall what the fix is.
yes I mean it is misaligned (the value measure) with respect to the dimensions.
at the moment it appears at the 'top'
In the father level view worksheet, if you look at the status bar at the bottom of the view you can see that even though there are 2 rows by 1 column, Tableau is drawing 3 marks. In a default view with only discrete (blue) pills on Columns & Rows, this triggers OO Stacking/non-Cartesian layout, which sets up to put the first mark in a cell in the upper left and then the next one to its right, and so on. The easiest way to change this is and get the number marks aligned like the others is to change Analysis->Stack Marks to Off. You can also use a table calculation filter to get the same effect, for example you could Ctrl+Drag a copy of the WINDOW SUM calc onto the Filters Shelf and select Special tab->non-Null Values. I tend to use the latter in most cases, because in views with a lot of marks this can improve performance.
Thanks for that. I see that turning Stack Marks to Off does change the alignment, but when I filter to Non-Null values, I do not get the same thing. Am I missing something obvious?
Nevermind, it seems to be working fine now. Maybe I pulled the Window_Sum from the measures list in the data window, instead of CTRL+clicking to drag it to the filters shelf.
Jimmy--I've re-attached your workbook with the format I assume you want.
genius jonathan and mat.