Average weighted part pricing

Question

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.&nbsp; I would also need this weighting to adjust based on the filters applied.&nbsp; An example of a line item is below.&nbsp;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.&nbsp;Any insight/suggestions would be greatly appreciated.Supplier&nbsp;&nbsp;&nbsp;&nbsp; Year&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Invoice Spend&nbsp;&nbsp;&nbsp;&nbsp; Invoice Price&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;Part #&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Time PeriodABC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2015&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$650&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $6.50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Z867&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1/1/13

mattallington · Answer

Yeah, sorry that wasn't quite right. &nbsp;It is always better/easier if you create a sample workbook and post it. &nbsp;That will get the fastest and most accurate results. &nbsp;&nbsp;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] )
    )))&nbsp;Weighted Price:=DIVIDE([Total Spend] , [Total Qty])&nbsp;&nbsp;Take a look at my Excel book here.https://www.dropbox.com/s/vg1ztg9vr8542sh/weighted%20price.xlsx?dl=0&nbsp;

mattallington · Answer

I would think something like this should work.&nbsp;=SUMX&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;VALUES&nbsp;(&nbsp;Data[Part&nbsp;#]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;(CALCULATE&nbsp;(&nbsp;MAX&nbsp;(&nbsp;Data[Invoice&nbsp;Price]&nbsp;),&nbsp;LASTDATE&nbsp;(&nbsp;Data[Time&nbsp;Period]&nbsp;)&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&nbsp;CALCULATE&nbsp;(&nbsp;MIN&nbsp;(&nbsp;Data[Invoice&nbsp;Price]&nbsp;),&nbsp;FIRSTDATE&nbsp;(&nbsp;Data[Time&nbsp;Period]&nbsp;)&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;*&nbsp;CALCULATE&nbsp;(&nbsp;SUM&nbsp;(&nbsp;Data[Invoice&nbsp;Spend]&nbsp;)&nbsp;))&nbsp;/&nbsp;SUM&nbsp;(&nbsp;Data[Invoice&nbsp;Spend]&nbsp;)&nbsp;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.&nbsp;

eburke133 · Answer

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

Forum Discussion

Average weighted part pricing

3 Replies

Related content

Calculating Weighted Average

Weighted Average DAX

Weighted Average Measure

Weighted average by month

Weighted Average