Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

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

Highlighted
5 - 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!
Highlighted
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.
Highlighted
5 - 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