Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

% sum not totaling to 100- HELP

Highlighted
8 - Asteroid

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?

 

hash_89_1-1587942707356.png

 

Have attached my workflow for your reference. 

 

Thanks!

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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.

AbhilashR_0-1587943594462.png

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

Highlighted
8 - Asteroid

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

Highlighted
Alteryx
Alteryx

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

 

Capture.PNG

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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.

AbhilashR_0-1587952097300.png

 

Highlighted
8 - Asteroid

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. 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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.

AbhilashR_0-1587953541140.png

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

Highlighted
8 - Asteroid

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

 

hash_89_0-1587953636048.png

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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

Highlighted
8 - Asteroid

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?

 

hash_89_0-1588651622854.png

 

 

Labels