Forum Discussion

eburke133's avatar
eburke133
New Member
10 years ago

Average weighted part pricing

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...
Need Help
MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
10 years ago

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's avatar
    eburke133
    New Member
    10 years ago

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

    • MattAllington's avatar
      MattAllington
      Icon for Community Champion rankCommunity Champion
      10 years ago

      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