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

[junker]

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!

[MarqueeCrew · Accepted solution]

@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

[junker]

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!