6 Replies Latest reply on May 14, 2012 12:57 PM by huanhuan wang

# how to find percentage of positive count in a table?

Hi,

I've been struggling with this calculation. I want to calculate the percentage of products that have positive impact across all products for different quarters. so for example, for each quarter, i want to see there are 50% of products that are positively being impacted. everytime i try to count the positive impact using 'if >0, then 1, else 0' for each quarter, it gives me 1, same for the products, because its counting the data only showing in the current window. it's a simple hand calculation, but i haven't figured out how to do this in tableau. Anyone can help?

Thanks a lot!

• ###### 1. Re: how to find percentage of positive count in a table?

Hi Huanhuan,

I believe you're on the right track. You'll just have to do a second calculation. The above calculation would need to be used in something similar to the following:

sum([above calculation])/total(count([products]))

Hope this helps!

-Tracy

1 of 1 people found this helpful
• ###### 2. Re: how to find percentage of positive count in a table?

Hi Tracy,

Thanks for the reply, i've tried that earlier and it still doesn't work. it does not allow me to use the sum function saying the calculation is already an aggregated value. so i just use '[above calculation]/total(count[products]). The numerator kept returning 1 instead of the actual count for 'above calculation'.

thanks,

Huanhuan

• ###### 3. Re: how to find percentage of positive count in a table?

Huanhuan,

Would you be able to post your packaged workbook (twbx file) or screenshots of the view and calculation?

-Tracy

• ###### 4. Re: how to find percentage of positive count in a table?

Hi Tracy,

i think the problem comes from my calcualtion for the 'impact', i used sum functions to calcualte the impact so later when calculate the differences for impacts, it's automatically becomes aggregated values. here is the example. i have to use the sum function to find the impact because the different granularity levels. and another problem is when using sum, it sums everything instead of summing for distinct products (if positive, sum only once).

Million thanks,

Huanhuan

• ###### 5. Re: how to find percentage of positive count in a table?

Hi Huanhuan,

You can change the positivecount calculation to be the following:

window_sum(if sum([meet goal 1])/sum([total])-sum([meet goal 2])/sum([total])>0

then 1

else 0

end)

Then, the %positive calculation should work. A couple of things to note:

1. Since this is now a table calculation, once the calculation is on the view, right click on the calc and select Compute Using-->Table (Down)

2. The grand total for positivecount will be incorrect.

Hope this helps!

-Tracy

1 of 1 people found this helpful
• ###### 6. Re: how to find percentage of positive count in a table?

Hi Tracy,

Thanks for the tip with window_sum! it solves the aggregate problem. however the value doesn't seem right. so finally i gave up the aggregation and use some raw value from excel and changed the comuting using 'cell', it returns the correct answer for non-aggregated values.

Billion thanks,

Huanhuan