
1. Re: Row Total ignoring few values
Michel Caissie Jun 21, 2016 8:54 AM (in response to zibal jafri)Zibal,
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) )
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
Michel

Total (ed mc).twbx 19.0 KB


2. Re: Row Total ignoring few values
zibal jafri Jun 21, 2016 9:29 AM (in response to Michel Caissie)Hi Michel,
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.
Regards,
Zibal

Percent.twbx 16.7 KB


3. Re: Row Total ignoring few values
Michel Caissie Jun 21, 2016 2:36 PM (in response to zibal jafri)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.

4. Re: Row Total ignoring few values
zibal jafri Jun 21, 2016 5:03 PM (in response to Michel Caissie)Hi Michel,
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:
Get Denominator:
If ATTR([Category]) = "Eating"
then
((AVG([Net Hours])))  LOOKUP(SUM([Time Spent]), 1)
ELSEIF
ATTR([Category]) = "Leisure"
then
((AVG([Net Hours])))  LOOKUP(SUM([Time Spent]), 2)
ELSEIF
ATTR([Category]) = "Sleeping"
then
((AVG([Net Hours])))  LOOKUP(SUM([Time Spent]), 3)
ELSEIF
ATTR([Category]) = "Till Date"
then
((AVG([Net Hours])))  LOOKUP(SUM([Time Spent]), 4)
ELSEif
ATTR([Category]) = "DO Nothing"
then
AVG([Net Hours])
END
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.
Regards,
Zibal

TotalPercent.twbx 14.9 KB
