Multi-Row Formula Tool Question: Calculate Percent Of Total
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Solved! Go to Solution.
- Labels:
- Best Practices
- Dynamic Processing
- Help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@ValeriaK one way of doing this
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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! 😊
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
something like this - but @binuacs solution is just as good.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
![](/skins/images/3A331B3922338C3D350C8AB30CC1872B/responsive_peak/images/icon_anonymous_message.png)