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):
<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.
Is there a way to do this in a scalable way with measures?
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}Part B = ($30+$15)/(20+15) = $1.29
Is there a way to do this in a scalable way with measures?