There is a little trick to do this.
First thing to understand is that Tableau when computing the Total removes the level of granularity.
If you check on sheet 3 , I have a simple computation of the attribute function ATTR(Category) and for every row Tableau returns the Category itself ( obviously for each category there is a single attribute) but for the Total, Tableau display a * because there is not a single value of Category in the View.
We can take advantage of this behavior by replacing the TimeSpent with the following calculation
//return value for each cell
if ATTR( [Category] ) = ATTR( [Category] ) then SUM( [Time Spent] )
//return value for Total
else SUM( (if [Category] != 'DO Nothing' then [Time Spent] end) )
So for each row ATTR( [Category] ) = ATTR( [Category] ) will return true and display the Time Spent in each cell but when computing the Total it will return false, in which case you will display the adjusted sum of Time Spent without considering the category `DO Nothing
Total (ed mc).twbx 19.0 KB
Thanks for your response. That's really a great way to deal with such problems. I am facing another problem now. I have created a calculated field 'Percent' which calculates the percent of time spent on each category. When I am trying to get the Row Total for the percent field excluding "DO Nothing" value, I am getting no result as shown below:
I can't understand why. Could you please help me with this issue ? Attached is the file for reference.
Percent.twbx 16.7 KB
All you need to do is to apply to same logic I did.
You already have the Percent for each cell, what is missing is the computation for your Grand Total.
I dont know what kind of percentage exactly you are looking for the Grand Total but
you can get the Time Spent sum using the previous example.
SUM( (if [Category] != 'DO Nothing' then [Time Spent] end) )
For the denominator I dont know exactly what you need but you can get the AVG of Net Hours using a similar function.
AVG( (if [Category] != 'DO Nothing' then [Net Hours] end) )
In other words , build a calculation that would give you the expected result on a worksheet with only Name on Rows, and use this calculation in the //return value for Total of the previous example.
Thanks for your response. The problem is that the calculated field (Percent) is already an aggregated field. So when I want to sum the Percent while returning the total by your method, I get an error saying that the argument passed to sum function is already aggregated and can't be aggregated further. The calculated field (Percent) is calculated as below:
Sum ([Time Spent ])/ Get Denominator
Where Get Denominator is again a calculated field calculated as:
If ATTR([Category]) = "Eating"
((AVG([Net Hours]))) - LOOKUP(SUM([Time Spent]), -1)
ATTR([Category]) = "Leisure"
((AVG([Net Hours]))) - LOOKUP(SUM([Time Spent]), -2)
ATTR([Category]) = "Sleeping"
((AVG([Net Hours]))) - LOOKUP(SUM([Time Spent]), -3)
ATTR([Category]) = "Till Date"
((AVG([Net Hours]))) - LOOKUP(SUM([Time Spent]), -4)
ATTR([Category]) = "DO Nothing"
I have tried so many things to resolve this issue but no luck so far. Please help me if possible. Attached is the twbx for your reference.
TotalPercent.twbx 14.9 KB