Based on the pointers given by the excellent article by Jonathan in the link below, I have been able to solve the problem myself.
Here is the output I got
I have tested to find out whether we are in a detail or grant total row and then outputted a different value for the measure based on the results. So, it works like a charm. Thanks a lot, Jonathan Drummey
But now I have a different problem - the reference line is coming for the the grand total row also, which does not make sense - can anyone let me know how to turn it off for grand total alone?
1 of 1 people found this helpful
You're welcome! To remove the reference line from the Grand Total, the workaround is to build your own calculation using one of the custom grand total techniques that returns the desired reference line values in the detail rows and Null in the Grand Total row. Looking at your screenshot (answering from my phone here) I'm guessing that will need to be a table calculation.
Hi Jonathan, I did not notice your reply since one of the Gmail's rules took the notification straight into a folder . Thanks for telling me that I can customize the reference line using a calculation. Let me try this out first and if I get stuck, I would ask for help again.
Thanks a lot for the clue - I did not know that we can add a custom calculation in the reference line area. When I went to the edit window of the reference line, it displays only the measures that are used in the view. Hence, i had to add the custom calculated field for reference line to Detail in order to display it - is the right way to do it?
Regarding your comment that this calculation would probably be a table calculation, can you please tell me why you thought of it that way? I have not used a table calculation here - here is the formula I have used:
Net Score % - Reference Line =
IF MIN([Attribute]) != MAX([Attribute]) THEN
//Grand Total Row - Return NULL as we do not want the reference line to be displayed here
SUM([Net Score])/[Number of Responses Window Sum]
1 of 1 people found this helpful
The calc you used is a table calculation, since you're using the WINDOW_SUM() in the Number of Responses Window Sum calc. However, the way you generated the average is by using the Average of the results of the Net Score - % Reference Line measure in each pane, so it's the (Tableau computed) average of the table calculation.
I'm not sure the view is getting you the results that you are expecting, though. You've got the WINDOW_SUM([Number of Responses]) measure set to Compute Using with Table (Across), which with this arrangement of dimensions in the view is partitioning on Group & Attribute, which effectively gives the measure the same results as the COUNTD([Response Id]) that is the [Number of Responses] measure. I set up this view to show that:
Thanks for the pointers and the questions to make me double-check everything again. I appreciate your inputs.
The reference-line calculation is calculating the correct results. Your observation regarding the use of WINDOW_SUM as being ineffective in the current view is spot-on, though I have created the Window Sum to be used in another view which breaks down each attribute into rating - hence the window sum and the number of responses measure will produce different results. For the sample results here, I have removed those details. In any case, your questions made me relook everything once again to ensure that things are working the way they are supposed to work - hence, I appreciate your time and inputs. Thanks and have a nice day Jonathan.