Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

% sum not totaling to 100- HELP

hash_89
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!

 

9 REPLIES 9
AbhilashR
15 - Aurora
15 - Aurora

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.

hash_89
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. 

echuong1
Alteryx Alumni (Retired)

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

AbhilashR
15 - Aurora
15 - Aurora

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

 

hash_89
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. 

AbhilashR
15 - Aurora
15 - Aurora

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.

hash_89
8 - Asteroid

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

 

hash_89_0-1587953636048.png

 

RolandSchubert
16 - Nebula
16 - Nebula

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

hash_89
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