topic Re: % sum not totaling to 100- HELP in Alteryx Designer Desktop Discussions

% sum not totaling to 100- HELP

hash_89 — Sun, 26 Apr 2020 23:17:54 GMT

Hi,

I need to calculate % of the total and report it out. However the sum of it doesn't total to 100. It is either > or < 100. Can someone help me how i can fix this?

Have attached my workflow for your reference.

Thanks!

Re: % sum not totaling to 100- HELP

AbhilashR — Sun, 26 Apr 2020 23:31:46 GMT

Hi @hash_89, convert the numerical columns into Double datatype and then do the computation. The final result comes out to be 100 in this case, after which you can convert it to fixed decimal with two decimal places if that's how you want your output to look like.

I have attached a modified version of your file for your reference.

Re: % sum not totaling to 100- HELP

hash_89 — Mon, 27 Apr 2020 00:02:59 GMT

Thanks for your response. I tried converting it back to fixed decimal with two decimal places, but still the total comes to 100.3.

Re: % sum not totaling to 100- HELP

echuong1 — Mon, 27 Apr 2020 01:30:48 GMT

The differences are due to rounding - I noticed you're using a fixed decimal with two decimal places as your data type and also rounding to two decimal places in your formula. Try expanding the number of decimal places. I expanded it to a fixed decimal of 26 digits with 10 decimal places and am seeing a total of 100%. See attached.

The difference between the different types of numeric formats is the level of precision. This can affect your overall outcome.

https://help.alteryx.com/current/Reference/DataFieldType.htm

Re: % sum not totaling to 100- HELP

AbhilashR — Mon, 27 Apr 2020 01:48:34 GMT

Hi @hash_89, covert it back to Fixed Decimal only after you divide and summarize the data. I convert if back to Fixed Decimal only in the last Select Tool.

This is what I get as an output.

Re: % sum not totaling to 100- HELP

hash_89 — Mon, 27 Apr 2020 01:59:55 GMT

Thanks. My final O/P needs to have % total in fixed decimal (19.2 size) and that totaling to 100%. I tried to change the data type of column % of total in select tool , but still the sum is 100.3.

Re: % sum not totaling to 100- HELP

AbhilashR — Mon, 27 Apr 2020 02:12:56 GMT

Sorry, I am confused. I get my output as 100.00 in Sum_% of total column. That is what you too are looking for, correct?

In order to get 100 and not 100.03 as your output:

convert all numerical columns into Double data type upstream
do the division in formula tool (ensure this formula has Double datatype)
summarize the result set in Summarize tool
finally, convert the % of total back into Fixed Decimal datatype with a precision of 19 and a scale of 2 in the final Select tool before output

Screenshot of my final output.

I hope this helps. Reattaching my solution for your reference.

Re: % sum not totaling to 100- HELP

hash_89 — Mon, 27 Apr 2020 02:14:51 GMT

My O/P should be those individual % of total values. This should sum to 100%.

Re: % sum not totaling to 100- HELP

RolandSchubert — Mon, 27 Apr 2020 10:55:32 GMT

Hi @hash_89 ,

as @AbhilashR and @echuong1 already explained, the difference is caused by rounding. The only solution I can imagine is to add a "correction" to the rows having the maximal difference between rounded and unrounded percentages. In your sample, there are 3 rows with a rounding difference of approx. 0.005 %, I would adjust these rows by 0.01%. I've attached a sample workflow.

Let me know, if this works for you.

Best,

Roland

Re: % sum not totaling to 100- HELP

hash_89 — Tue, 05 May 2020 04:09:11 GMT

Thanks for your solution. If i try to do an average over and above this, i have the same issue irrespective of the data type that i use. How can i fix this?