Average weighted part pricing

[eburke133]

Is there a function(s) in Power BI that would calculate an average weighted pricing change over time? Weighted by the amount of spend in 2015.  I would also need this weighting to adjust based on the filters applied.  An example of a line item is below. 

Each line item represents an invoice.
There can be multiple suppliers for each part #.
The baseline for each part needs to be the earliest invoice date of that part. The furthest back the data goes is 1/1/13.
I would like to be able to pull up a chart and see and average weighted pricing change for each supplier.

Any insight/suggestions would be greatly appreciated.

Supplier     Year      Invoice Spend     Invoice Price       Part #      Time Period

ABC            2015        $650                      $6.50              Z867         1/1/13

[MattAllington]

I would think something like this should work.

=SUMX (
    VALUES ( Data[Part #] ),
    (CALCULATE ( MAX ( Data[Invoice Price] ), LASTDATE ( Data[Time Period] ) )
            - CALCULATE ( MIN ( Data[Invoice Price] ), FIRSTDATE ( Data[Time Period] ) )
    )  * CALCULATE ( SUM ( Data[Invoice Spend] ) )
)

/ SUM ( Data[Invoice Spend] )

Ideally you would have a product table and a calendar table and swap out the VALUES and the FIRST/LASTDATE portions with columns from those tables.

[eburke133]

Thanks for the help here.  I am still having issues getting this to apply correctly.  Can you elaborate on the two tables?

[MattAllington]

Yeah, sorry that wasn't quite right.  It is always better/easier if you create a sample workbook and post it.  That will get the fastest and most accurate results.  

Total Spend:=sumx(values(Data[Part #]),calculate(SUMX ( Data,Data[Invoice Spend])))

Total Qty:=sumx(values(Data[Part #]),calculate(SUMX (
         Data,
        DIVIDE ( Data[Invoice Spend], Data[Invoice Price] )
    )))

Weighted Price:=DIVIDE([Total Spend] , [Total Qty])

Take a look at my Excel book here.

https://www.dropbox.com/s/vg1ztg9vr8542sh/weighted%20price.xlsx?dl=0