question on measures

[wiz329]

Hi guys, I'm fairly competent with excel (array formulas, macros, etc.), but new to powerpivot. I'm wondering if it's possible to do the following:

My data structure is as follows (give or take with table relationships etc. removed for simplicity):

Group Name	Part Name	Transaction ID	Quantity	Total Price
Group A	Part A	OID123	10	$20
Group A	Part A	OID124	15	$5
Group A	Part B	OID125	20	$30
Group A	Part B	OID126	15	$15
Group B	Part C	OID127	10	$35
Group B	Part C	OID128	5	$30
Group B	Part D	OID129	10	$10
Group B	Part D	OID130	15	$25

<tbody>
</tbody>

Basically, I'm trying to create a measure that will do the following:

First, I need to aggregate to an average selling price at the part level (not transaction price). So, I'm pretty sure the measure I would write would be the following: Average Selling Price = SUM ([Total Price]) / SUM([Quantity]).

The next part is to find percentiles, maxes, and mins of these part-level ASPs.

So, I need to find the 90th p-tile (or median, max, etc.) of all of the Average Selling Price Values for each part (*not* transaction) for each group.

In this case, then, to find the MAX ASP for all parts in Group A, I would do the following:
1. Find ASP for Part A and Part B.

Part A = ($20+$5)/(10+15) = $1.00
Part B = ($30+$15)/(20+15) = $1.29​

2. So the MAX would be $1.29. MAX{$1.00,$1.29}

Is there a way to do this in a scalable way with measures?

 

[wiz329]

Also, any commentary on the differing approaches from citizenbh and ozeroth?