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

Calculate row value as a proportion of the sum of all rows using In-DB

akasubi
8 - Asteroid

How can I calculate the value of one row of data as a proportion of the sum of all rows in the data, using In-DB?

 

E.g. 

IDValueProportion
A1010%
B3030%
C2020%
D4040%

 

The total sum of all rows is 100.

 

 

Thanks

afk

5 REPLIES 5
Thableaus
17 - Castor
17 - Castor

Hi @akasubi 

 

Which DB are you using?

 

This will definitely involve a Summarize Tool, to calculate the total column.

Then you might need to append this total to all of your rows, and do something like (Value/Total) to calculate the proportion.

 

You could give us a better picture of your data and specify which database you're working with.

 

Cheers,

akasubi
8 - Asteroid

Hi @Thableaus 

 

I'm connected to SQL server. I did think to do as you suggested but wasn't sure if this was the correct approach.

 

From another question I posted, I will need to create dummy fields to append the total sum to all my rows?

Thableaus
17 - Castor
17 - Castor

@akasubi 

 

Either it's that or creating a SQL query to do this calc for you (from what I know)

 

Using Alteryx In-DB tools, I think this is the best way to go (the most intuitive one).

 

Creating the Total Column should not be a problem. You can drop it after calculating the Proportion column.

 

Cheers,

geraldo
13 - Pulsar
Hi, The query that I suggest looks like this: select ID as ID, count(*) as Value , count(*) / max( (select sum(Value) from ( select count(*) as Value , ID from TableName group by ID) )) * 100 as Proportion from TableName group by ID;
geraldo
13 - Pulsar

Hi,


The query that I suggest looks like this:


select ID as ID,
count(*) as Value ,
count(*) / max( (select sum(Value)
from ( select count(*) as Value ,
ID
from TableName group by ID) )) * 100 as Proportion
from TableName group by ID;

 

 

Labels