Double AGGR Function in a Table

[spjuza]

I have a table with a variety of costs and I'm needing to get the 50th Percentile Cost within a feature.  As I understand it, I will need to do two aggregations, one to get the total amount spent per product and then one to get the 50% cost within the feature. 

Sample data table:

Product ID|Feature 1|Quanity|Spend

1|1|100|$1000

1|1|100|$1500

2|1|20|$220

So the results would look like this:

Product ID|Feature 1|Quanity|Spend|Average Cost per Product ID|50th Percentile within Product ID|50th Percentile within Feature

1|1|100|$1000|$10|$12.5|$11

1|1|100|$1500|$15|$12.5|$11

2|1|20|$220|$11|$11|$11

...

I have the code for how to get the Average cost per product, that was easy. 

Fractile(aggr(sum([Spend])/sum([Quantity]),[Product ID]),.50)

However, trying to get that into a second aggregation has proved tricky.  Wrapping everything in an additional Aggr doesn't return the expected values.

Fractile(aggr(aggr(sum([Spend])/sum([Qty]), [Product ID]),[Feature 1]),.50) 

Thoughts on how to proceed?

EDIT:  Someone at work pointed out that one of the issues is that you are overriding dimensions to show other data and suggested to use the Total function, but still not entirely sure how to go about that. 

[deepanshuSh]

Maybe this could work, You can add the brackets and fields based on your requirement. 

Fractile(Aggr(Sum(Total(aggr(sum([Spend])/sum([Qty]), [Product ID])),[Feature 1])),.50) 

My suggestion would be to use variables rather than the direct equations. 

[spjuza]

Unfortunately, this didn't seem to work.  It was also missing one of the dimensions within one of the AGGR commands, but whether I put in Product ID or Feature 1, it didn't matter. 

[vinieme12 · Accepted solution]

as below

temp:
load *,recno() as ID inline [
ProductID|Feature 1|Quanity|Spend
1|1|100|$1000
1|1|100|$1500
2|1|20|$220
]
(delimiter is '|');

exit Script;

Dimensions

ID, ProductID,Feature1

Measures

Quantity  = Sum(Quantity)

Spend= Sum(Spend)

Average Cost per Product ID =   Sum(Spend)/ Sum(Quantity)

50th Percentile within Product ID =  Fractile(TOTAL <ProductID> Spend/Quanity,0.5)

50th Percentile within Feature =  Fractile(TOTAL <[Feature 1]> Spend/Quanity,0.5)