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?

 

[Rhodie72]

When you say measure/measures I'm assuming you mean formulas/formulae?

 

[wiz329]

When you say measure/measures I'm assuming you mean formulas/formulae?

No. I'm guessing (since this is done in Power Pivot), these will need to be done in the Data Model with measures, no?

 

[Rhodie72]

I don't know what measures are. Can't help you on this otherwise. Formulas or VBA: yes. Measure? no. never heard of it.

 

[Ozeroth]

Hi wiz329,

Assuming you have defined an [Average Selling Price] measure, you can then use iterator functions to calculate average/max/min, using VALUES ( YourTable[Part Name] ) as the first argument for the iterators.

For the average & max, the pattern would look like this:

Average Selling Price By Part
=
AVERAGEX (
    VALUES ( YourTable[Part Name] ),
    [Average Selling Price]
)

Maximum Selling Price By Part
=
MAXX (
    VALUES ( YourTable[Part Name] ),
    [Average Selling Price]
)

If you create a PivotTable with, say, Group on the rows, these measures would give you the average/max for Parts within each Group.

In Excel 2010/2013, the iterators available include AVERAGEX, MAXX, MINX, STDEVX.P, STDEVX.S, VARX.P, VARX.S.

Unfortunately PERCENTILEX.INC, PERCENTILEX.EXC, MEDIANX (and maybe some others I've missed) were only introduced in Excel 2016 (and Power BI).

Check out DAX Patterns for ways to define these yourself:
Statistical Patterns – DAX Patterns

Owen

 

[citizenbh]

Try this:
1. Create the following measures:

SumPriceParts:=CALCULATE( SUM( [Total Price]); ALLEXCEPT(YourTable; YourTable[Part Name]))
SumQuantityParts:=CALCULATE( SUM( [Quantity]); ALLEXCEPT(YourTable; YourTable[Part Name]))
MaxPriceGroup:=CALCULATE( MAX( [ASP]); ALLEXCEPT(YourTable; YourTable[Group Name]))

2. Add "ASP" column:

=[SumPriceParts]/[SumQuantityParts]

3. Create Pivot Table with ROWS

- Group Name, Part Name and ASP

4. In Ʃ VALUES add MaxPriceGroup

 

[wiz329]

Try this:
1. Create the following measures:

SumPriceParts:=CALCULATE( SUM( [Total Price]); ALLEXCEPT(YourTable; YourTable[Part Name]))
SumQuantityParts:=CALCULATE( SUM( [Quantity]); ALLEXCEPT(YourTable; YourTable[Part Name]))
MaxPriceGroup:=CALCULATE( MAX( [ASP]); ALLEXCEPT(YourTable; YourTable[Group Name]))

2. Add "ASP" column:

=[SumPriceParts]/[SumQuantityParts]

3. Create Pivot Table with ROWS

- Group Name, Part Name and ASP

4. In Ʃ VALUES add MaxPriceGroup

Hey guys, thanks for the input! I haven't had a chance to take a look yet, but really appreciate your responses. I'll let you know how it goes!

 

[wiz329]

Try this:
1. Create the following measures:

SumPriceParts:=CALCULATE( SUM( [Total Price]); ALLEXCEPT(YourTable; YourTable[Part Name]))
SumQuantityParts:=CALCULATE( SUM( [Quantity]); ALLEXCEPT(YourTable; YourTable[Part Name]))
MaxPriceGroup:=CALCULATE( MAX( [ASP]); ALLEXCEPT(YourTable; YourTable[Group Name]))

2. Add "ASP" column:

=[SumPriceParts]/[SumQuantityParts]

3. Create Pivot Table with ROWS

- Group Name, Part Name and ASP

4. In Ʃ VALUES add MaxPriceGroup

Quick tactical question -- what do the semicolons do in a measure? I'm working my way through Rob Collie's book on PowerPivot, but haven't run into this yet.

EDIT: Ahh, I see it's just an alternative to using commas. What's the advantage?

 

[akice]

Not based on advantages/disadvantages. They are used as argument separators determined by locality of install. USA and UK use comma's I think. Everywhere else they use semi-colons. similar to MM/DD/YYYY versus DD/MM/YYYY for dates.

 

[wiz329]

Cool, thanks for the context.