Multi-Row Formula Tool Question: Calculate Percent Of Total

[ValeriaK]

Hello Alteryx Community,

I'm seeking your expertise in using the Multi-Row Formula tool to achieve specific calculations.

Here's a simplified version of my task:

Objective: Calculate the percentage of "Flag 1" (Sub Category) out of the "Grand Total" (Flag 2) for each column (7, 14, 30, 60, 90, and 120 days).

Example: Input Data:

Desired Output:

Your insights and suggestions are greatly appreciated.

Thank you!

[OTrieger]

@ValeriaK 
Have you tried the following formula:
Row +1 = Row 0/ Row -1?

I never tried it before, therefore asking if you tried it?
The thing is that you can do it only for one field at a time
So it will be better to  Transpose the data create the 3 columns and then flip it to rows.

[OTrieger]

Or you can do it in a batch macro one field at a time and at the end you will get the full table. But it will require more transformation so each time the automation will take only one field. 

[ValeriaK]

@OTrieger Thanks for the solution ideas.
I'm having a bit of trouble testing the formula you mentioned because I've not used this tool for calculation purposes before.
I'm not sure how to go about testing the formula myself.
I've attached a mockup of the data I'm working with (initial post)
Would you be able to help me out with this? I'd really appreciate it!
Thanks!

[binuacs · Accepted solution]

@ValeriaK one way of doing this

[apathetichell]

Try:

Transpose. use flag as a key.

use multi-row formula -> update value - group by [Name]

I used: 

if contains([Flag],"Total") then [Value]-[Row+1:Value] else [Value] endif - which assumes your FIRST row is your total.

cross tab - flag is your key field/ name  is your columns - value is your rows - > IMPORTANT set it to percent column.

you now have your percents - so you have to do some things to clean it.

1) use a formula tool to set your flag to "% column" or whatever

2) use a formula tool to set percent as you want.

use a sample tool - set it for 1. don't group.

connect dynamic rename

select all fields.

replace([_CurrentField_],"XCol_","")

use a union tool.

[ValeriaK]

Good morning, Alteryx Gurus!

Thank you all for your fantastic suggestions!

Shoutout to @binuacs!🙏

Your solution works great for my needs, and I really appreciate the time you took to prepare the demo.

Have a great day, everyone!

[ValeriaK]

@apathetichell 👋

Thank you for your help!
Your suggestion is another great solution and I'm really keen to keep learning.

There's just one thing I'm still struggling with.

Would it be possible to add a demo to the actual workflow?

I think that would really help me get my head around the whole process.

I'm really passionate about learning this stuff, and I know that having a visual representation of the workflow would make a huge difference.

Thank you in advance! 😊

[apathetichell · Accepted solution]

something like this - but @binuacs solution is just as good.

[ValeriaK · Accepted solution]

@apathetichell 

Thanks a bunch for sharing your idea!🙏

I'm always excited to learn new ways to tackle tasks.

Saving it to my knowledge library.

I really appreciate you taking the time to share your thoughts.

Have a fantastic rest of your day!
Posting all solutions combined.