Forum Discussion
Weight Average
Hello, I'm looking to get help with a formula that will get me the weighted average based off a set of paramters. I would like the formula to take the sum of spend per material number and divide ...
This the DateAdd (-1 Year) option instead of the DATE command..... (Sorry for the change in TABLE name and columns in the version below vs. my original post.) But you only have to change the FILTER Portion to use DATEADD vs. DATE(Y)(M)(D)
1_Year_Avg_Spend = CALCULATE( SUM( 'Table'[USD Spend]) / SUM('Table'[Qty]), FILTER( 'Table', 'Table'[Document Date] > DATEADD('Table'[Document Date], -1, YEAR)))Try this....
Last_Pur_Price = CALCULATE( AVERAGE( 'Table'[Part Price]), FILTER('Table', 'Table'[Document Date] = [Last_Pur_Date]))If you had multiple sales on the same day, assuming you want the average of the two?
Hi,
Share some data to work with. The data should be such that can be pasted in an MS Excel file. Alternatively, share the link from where i can download your PBI file.
Ashish_Mathur Let me know if this helps
| Part Number (Material) | Part Price (USD) (Piece Price) | Quantity | UOM (Unit of Measure) | USD Spend | Document Date |
| PN-272679 | $35.05 | 9 | EA | $315.42 | 3/14/2019 |
| PN-272679 | $23.54 | 3 | EA | $70.63 | 4/1/2019 |
| PN-272679 | $21.37 | 96 | EA | $2,051.78 | 4/1/2019 |
| PN-272679 | $23.54 | 30 | EA | $706.28 | 4/1/2019 |
| PN-272679 | $34.88 | 9 | EA | $313.90 | 4/15/2019 |
| PN-272679 | $21.62 | 69 | EA | $1,491.70 | 4/16/2019 |
| PN-272679 | $21.57 | 75 | EA | $1,617.49 | 4/29/2019 |
| PN-272679 | $24.94 | 24 | EA | $598.67 | 4/29/2019 |
| PN-272679 | $23.44 | 24 | EA | $562.52 | 5/10/2019 |
| PN-272679 | $24.83 | 36 | EA | $894.03 | 5/30/2019 |
| PN-272679 | $22.42 | 48 | EA | $1,076.22 | 5/30/2019 |
| PN-272679 | $25.13 | 24 | EA | $603.11 | 6/10/2019 |
| PN-272679 | $25.13 | 24 | EA | $603.11 | 6/27/2019 |
| PN-272679 | $25.13 | 24 | EA | $603.11 | 6/27/2019 |
| PN-272679 | $22.11 | 96 | EA | $2,122.19 | 7/4/2019 |
| PN-272679 | $25.57 | 24 | EA | $613.65 | 7/25/2019 |
| PN-272679 | $22.11 | 84 | EA | $1,856.92 | 7/25/2019 |
| PN-272679 | $25.57 | 24 | EA | $613.65 | 7/25/2019 |
| PN-272679 | $22.00 | 60 | EA | $1,320.20 | 8/14/2019 |
| PN-272679 | $25.22 | 24 | EA | $605.36 | 8/31/2019 |
| PN-272679 | $25.22 | 24 | EA | $605.36 | 8/22/2019 |
| PN-272679 | $25.22 | 24 | EA | $605.36 | 8/31/2019 |
| PN-272679 | $22.00 | 72 | EA | $1,584.25 | 8/28/2019 |
| PN-272679 | $25.22 | 24 | EA | $605.36 | 8/31/2019 |
| PN-272679 | $25.25 | 24 | EA | $606.06 | 9/20/2019 |
| PN-272679 | $25.36 | 24 | EA | $608.52 | 10/10/2019 |
| PN-272679 | $25.35 | 36 | EA | $912.77 | 10/19/2019 |
| PN-272679 | $25.36 | 24 | EA | $608.52 | 10/24/2019 |
| PN-272679 | $25.28 | 24 | EA | $606.63 | 11/5/2019 |
| PN-272679 | $22.82 | 48 | EA | $1,095.38 | 11/14/2019 |
| PN-272679 | $22.05 | 60 | EA | $1,322.98 | 11/14/2019 |
| PN-272679 | $25.28 | 24 | EA | $606.63 | 11/14/2019 |
| PN-272679 | $25.28 | 24 | EA | $606.63 | 11/29/2019 |
| PN-272679 | $21.85 | 150 | EA | $3,277.98 | 11/27/2019 |
| PN-272679 | $25.56 | 24 | EA | $613.44 | 1/2/2020 |
| PN-272679 | $25.88 | 24 | EA | $621.14 | 1/14/2020 |
| PN-272679 | $25.56 | 24 | EA | $613.44 | 1/14/2020 |
| PN-272679 | $25.88 | 24 | EA | $621.14 | 1/23/2020 |
| PN-272679 | $23.02 | 40 | EA | $920.80 | 2/6/2020 |
| PN-272679 | $23.02 | 40 | EA | $920.80 | 2/10/2020 |
| PN-272679 | $23.02 | 40 | EA | $920.80 | 2/21/2020 |
| PN-272679 | $23.02 | 40 | EA | $920.80 | 2/20/2020 |
| PN-272679 | $22.01 | 40 | EA | $880.33 | 3/2/2020 |
| PN-272679 | $21.68 | 40 | EA | $867.35 | 3/25/2020 |
| PN-272679 | $21.71 | 40 | EA | $868.25 | 4/1/2020 |
| PN-272679 | $20.74 | 80 | EA | $1,659.14 | 4/7/2020 |
| PN-272679 | $22.29 | 40 | EA | $891.46 | 5/15/2020 |
| PN-272679 | $21.51 | 60 | EA | $1,290.49 | 6/4/2020 |
| PN-272679 | $22.06 | 60 | EA | $1,323.37 | 7/6/2020 |
| PN-272679 | $22.06 | 81 | EA | $1,786.55 | 7/21/2020 |
| PN-272679 | $22.52 | 60 | EA | $1,351.36 | 8/13/2020 |
| PN-272679 | $22.83 | 60 | EA | $1,370.02 | 9/3/2020 |
| PN-272679 | $22.45 | 60 | EA | $1,347.10 | 10/9/2020 |