Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

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

junker
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!
2 REPLIES 2
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
junker
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