community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Calculating Percentile and Median Unit Price using Summarize Tool with Quantity Field

Atom

Hello Alteryx Community!

 
I'm really stuck with something and decided to finally reach out for help after looking at the Community postings.
 
I have some transactional entries that I am working with as shown below:
 
Material     Quantity     Total Revenue   Unit Price
A                3                 6                        2
A                5                 15                      3
A                ...                ...                       ...
B                100             100                    1
B                25               31.25                 1.25
B                ...                ...                       ...
 
I am trying to calculate the Unit Price statistics for each material: 25th percentile, median, 75th percentile using the summarize tool. However, I need to make sure that I am properly counting each unit price by the quantity and not just counting each row as a single unit price as shown below for A:
 
2 2 2 3 3 3 3 3 rather than 2 3
 
There must be a way to do this but I just can't seem to figure it out. Any suggestions?!
 
Thank you!
Alteryx Certified Partner
Alteryx Certified Partner

@junker 

 

If you use the GENERATE ROWS tool, you can create 3 rows for the first input record (it would use the Quantity in a formula like:  RowCount <= [Quantity]) and then summarize using records that reflect the actual quantity per unit price.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Atom

Thank you so much for suggesting the Add Rows Tool. I was a little concerned about the coding with it, but it turned out to be easy when implemented as you described. Thank you so much!

Labels