AVG Cost Per Unit

[ketedford]

What I am trying to do is to create an additional column in a matrix visualization that looks at a UNIT ID and gets the AVG COST of that UNIT for a specific QTR by looking in the PRICING table and getting the AVG COST during that period. (A unit may have a price change in that qtr, multiple price changes, or a previous qtr change).

I have been going through different formulas and nothing gives me what I am expecting.

Here are my tables and the current MATRIX. Any assistance would be appreciated on how to create a measure on the AVG COST PRICE.

Thanks


I have 3 tables:

a) PRODUCT NAMES
UNIT ID, UNIT NAME

b) SALES
UNIT ID, YEAR, QTR, QTY UNITS SOLD, TOTAL $ SOLD

c) COST CHANGES
UNIT ID, UNIT COST, PRICE CHANGE



I have pulled a MATRIX VISUALIZATION together that correctly shows QTY UNITS SOLD, TOTAL $ SOLD and a measure using SUMX showing AVG SALE PER UNIT by YEAR and QTR. Seems to work Perfectly.

YEAR QTY UNITS SOLD TOTAL $ SOLD AVG SALES PER UNIT
2015
QTR 1
PRODUCT 1
QTR 2
PRODUCT 1
PRODUCT 2

...

 

[ketedford]

MY SAMPLE DATA I AM USING:

a) PRODUCT NAMES
UNIT ID UNIT NAME
1 Widget One
2 Widget Two
3 Widget Three

b) SALES
UNIT ID YEAR QTR QTY UNITS SOLD TOTAL $ SOLD
1 2015 1 6 $13.20
1 2015 1 8 $18.04
1 2015 3 4 $13.20
1 2015 4 5 $16.50
1 2016 2 4 $14.30
1 2016 2 4 $13.86
1 2016 2 10 $35.75
2 2016 1 2 $22.00
2 2016 2 6 $99.00
3 2016 1 9 $118.80

c) COST CHANGES
UNIT ID UNIT COST PRICE CHANGE
1 2 8/12/2014
1 2.05 3/1/2015
1 3 8/5/2015
1 3 9/15/2015
1 3.25 12/8/2015
1 3.15 2/28/2016
1 3.25 3/4/2016
2 10 8/1/2010
2 15 10/1/2012
3 12 12/31/2015

 

[gazpage]

Can you share your .pbix with expirebox or something?