DAX question weighted average across granularities

[jake32008]

Forgive my ignorance as I'm a bit of a noob but I am attempting to calculate the following two measures in PowerPivot:


FILL RATE @ PO LEVEL = [QTY RECEIVED]/[QTY ORDERED]
For example, in the table below, the [QTY RECEIVED] would be the sum of (10,0,125,15) and [QTY ORDERED] would be 150 (not the sum of {150,150,150,150})

DELIVERY COMPLIANCE RATE @ PO LEVEL = AVERAGE OF [DELIVERY COMPLIANCE] WEIGHTED BY QTY RECEIVED OUT OF TOTAL QTY ORDERED WHERE [DELIVERY COMPLIANCE] is a calculated column = IF([RECEIPT DATE] <= [REQUIRED DATE],1,0)

For example, again in the table below, [PO NO] 0027380 ordered 150 of [Item Code] 111248. 10k were received on [RECEIPT NO] 033243, 0 on [RECEIPT NO] 034122, 125 on [RECEIPT NO] 034180, 15 on [RECEIPT NO] 034223. The result I am looking for would be 6.7% delivery compliance (1*10,1*0,0*125,0*15) like an array formula.

I would also like it to roll up correctly using this same line of thinking to the item and vendor level.

Thank you in advance for your assistance in helping me better understand DAX!

VENDOR NAME	ITEM CODE	PO NO	RECEIPT NO	REQUIRED DATE	RECEIPT DATE	DELIVERY COMPLIANCE RATE	QTY ORDERED	QTY RECEIVED
BCS	111248	0027380	033243	2/13/2015	12/18/2014	100%	150	10
BCS	111248	0027380	034122	2/13/2015	2/13/2015	100%	150	0
BCS	111248	0027380	034180	2/13/2015	2/18/2015	0%	150	125
BCS	111248	0027380	034223	2/13/2015	2/23/2015	0%	150	15

<tbody>
</tbody>

 

[Kazlik]

One option is to make the following measures

QTYORD:=MAX(Sheet1[QTY ORDERED])
QTYRECEIVED:=SUM(Sheet1[QTY RECEIVED])
FILL RATE:=DIVIDE([QTYRECEIVED],[QTYORD])

DELIVERYCOMPLIANCEQTY:=CALCULATE(SUM(Sheet1[QTY RECEIVED]),FILTER(Sheet1,Sheet1[RECEIPT DATE] <= Sheet1[REQUIRED DATE]))
DELIVERYCOMPLIANCERATE:=DIVIDE([DELIVERYCOMPLIANCEQTY],[QTYORD])

 

[jake32008]

Kazlik! You are awesome my friend!

At that level it's gotten me exactly what I needed, but at the item level it adds up the QTYRECEIVED and returns 140%.

I just tried using IF(HASONEVALUE( to back it up to ITEM then VENDOR level to no avail. Any tips?

VENDOR	ITEM CODE	PO NO	RECEIPT NO	QTY ORD	QTYRECEIVED	FILL RATE	DELIVERYCOMPLIANCEQTY	DELIVERYCOMPLIANCERATE
BCS	111248	0027380	033243	150	10	6.7%	10	6.7%
			034180	150	125	83.3%	0	0%
			034223	150	15	10%	0	0%
	111248 TOTAL			150	210	140%	10	6.7
BCS TOTAL				385	2706	702.9%	1845500	479.4%

<tbody>
</tbody>

Thanks again!

 

[Kazlik]

I think if you add a new measure MAXQTYORD:=MAX(Sheet1[QTY ORDERED]) and then update the QTYORD:=SUMX(DISTINCT(Sheet1[PO NO]),[MAXQTYORD]) like this it should work.

 

[jake32008]

THANKS AGAIN!! I had to use the old noggin' a bit but I expanded it to QTYORD:=SUMX(DISTINCT(Sheet1[Item Code]),SUMX(DISTINCT(Sheet1[PO NO]),[MAXQTYORD]) ) and it now rolls up correctly all the way!

Jake

 

[ImkeF]

Nested SUMX - how cool is this !!

Look what you did: http://www.mrexcel.com/forum/power-bi/847179-how-do-you-calculate-field-calculated-fields.html

Thank you so much !!