New Columns with Proportions

[mvillicana]

Hi all! I'm new in Alteryx and I can't seem to find a way to create new columns with the proportion of each Type of income for every Company respect their revenues.

I'm trying to get something like this: (Company1_A_Revenue2017/Company1Sum_A+P_Revenue2017) and the for B in each Company and year.

I would really appreciate if any of you guys could show me how to to this calculation.

Thanks!

Name	Location	Type	Revenue_2017	%_2017
Company1	US	A	$                  8,000,000.00	44%
Company1	US	P	$               10,320,000.00	56%
Company2	MX	A	$               20,000,000.00	77%
Company2	MX	P	$                  5,935,000.00	23%
Company3	DR	A	$               15,000,000.00	53%
Company3	DR	P	$               13,080,500.00	79%
Company4	BR	A	$                  3,500,000.00	45%
Company4	BR	P	$                  4,200,000.00	55%
Company5	CH	A	$                  5,000,000.00	29%
Company5	CH	P	$               12,000,000.00	71%

[MarqueeCrew · Accepted solution]

@mvillicana,

Using your sample data, I created the following results:

My workflow takes all of the data through transpositions into a join where I've summed the revenue by year and then create proportion calculations dynamically across all years.  The data is then put back to its original format in a Cross Tab and joined to the original data as added columns.  Please review the workflow and let me know if there is more help needed.

Cheers,

Mark

[mvillicana]

Thank you Mark. This was really helpful. I already used the path for my original dataset and adapted and it works the way I wanted. :)

[MarqueeCrew]

You're welcome @mvillicana.  

Please take full advantage of the resources here in community, including asking questions.

Cheers,

Mark

[mvillicana]

Hi Mark, sorry to bother you again.

My goal is to do a data visualization that shows a green flag if A proportion (per company) is equal or greater than 80% in contrast with P proportion.

Is there a way I can create new columns per year with the conditional I'm trying to get?

I'm think something like IF(A=>.80,1,0).

Any video or screenshot would be very educational for me.

Attached you'll find my Excel data source and my Alteryx workflow.

Thank you in advance.

Pd: sorry for the concepts in Spanish.

[MarqueeCrew]

Hi @mvillicana,

I have amended the solution to include the "flag" field.  

Cheers,

Mark