DAX Formula for weighted average price

pjwhyman

New Member
Joined
Oct 29, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have a data set that is millions of rows long and has 100s of products listed in it. I would like to calculate an average weighted price per product per month, with the weighting based on the volume of the product.

My data looks like this (see mocked up table below) and in my data model it is connected to a basic calendar table that I use to group the data by month, year (etc).

I would like to write a measure using Dax that I can display in a pivot table (product in the rows, month in the columns, AWP in the data field). In the past, working with smaller datasets, I've done the calculation in an Excel workbook using several steps and Sumif formulas (etc). But that isn't practical with the larger dataset and I'd like the flexibility that a measure provides (so if I have to change periods, I don't have to rebuild a workbook.

I would really appreciate any help provided.

Thanks

Phil



Mock up data
CLIENT_IDPRODUCT_IDDATEEVENT_IDPRICEVOLUMESPEND
6SCEV19PRODUCT01
27/06/2019​
2229802​
40.72​
0.57​
$23.21​
2ATUV52PRODUCT01
30/09/2019​
4322943​
50.55​
1.27​
$64.20​
2ZRIW87PRODUCT01
4/10/2019​
5722919​
40.72​
0.71​
$28.91​
1ATEU55PRODUCT02
15/07/2019​
8622945​
27.8​
0.13​
$3.61​
5JCON29PRODUCT01
27/04/2019​
6822919​
34.59​
0.07​
$2.42​
4ILIP37PRODUCT01
30/08/2019​
7022891​
40.72​
0.71​
$28.91​
1XATY85PRODUCT01
2/10/2019​
6723571​
49.42​
0.46​
$22.73​
9NYQX23PRODUCT01
4/05/2019​
5622919​
40.72​
0.71​
$28.91​
3BPNA50PRODUCT01
6/01/2019​
3122961​
43.25​
0.57​
$24.65​
9FWNT81PRODUCT01
6/11/2019​
1233555​
50.55​
1.07​
$54.09​
3UJWH64PRODUCT01
2/02/2019​
9823355​
50.55​
0.38​
$19.21​
6ABSY44PRODUCT01
26/08/2019​
1522961​
43.25​
0.29​
$12.54​
4EMLC93PRODUCT01
19/09/2019​
9422954​
26.56​
0.55​
$14.61​
5RVPF51PRODUCT01
8/09/2019​
8222954​
26.56​
0.55​
$14.61​
2SLYO70PRODUCT02
25/12/2019​
5229230​
38.48​
1.13​
$43.48​
1PKWO46PRODUCT01
12/02/2019​
1002291​
34.59​
0.2​
$6.92​
6PQPQ67PRODUCT01
21/12/2019​
1022919​
34.59​
0.75​
$25.94​
6AOBO07PRODUCT01
1/10/2019​
1022954​
26.56​
0.28​
$7.44​
9ZDAH66PRODUCT01
22/11/2019​
3822943​
49.42​
0.06​
$2.97​
9KFOR05PRODUCT01
4/01/2019​
1235718​
49.42​
0.86​
$42.50​
4BUBX23PRODUCT01
22/06/2019​
5823355​
50.55​
1.27​
$64.20​
8RRSC80PRODUCT01
4/03/2019​
2422954​
50.55​
0.76​
$38.42​
2LTLA07PRODUCT02
17/02/2019​
1229275​
56.47​
0.67​
$37.83​
6IAMG20PRODUCT01
5/11/2019​
4229578​
34.66​
0.92​
$31.89​
8CIFO28PRODUCT01
2/12/2019​
6123355​
50.55​
1.27​
$64.20​
9MKOO02PRODUCT02
9/11/2019​
8822943​
30.89​
0.47​
$14.52​
7VOWM51PRODUCT01
10/01/2019​
8022919​
40.72​
0.57​
$23.21​
7MGOW86PRODUCT01
31/08/2019​
1722891​
40.72​
0.43​
$17.51​
5ITKT89PRODUCT03
14/12/2019​
2422919​
43.92​
0.29​
$12.74​
0WGOS42PRODUCT01
9/09/2019​
9122891​
40.72​
0.71​
$28.91​
2CUQR99PRODUCT02
2/02/2019​
3722923​
38.49​
1.43​
$55.04​
4NHNI85PRODUCT01
7/08/2019​
2322961​
43.25​
0.07​
$3.03​
9EDQV42PRODUCT01
2/07/2019​
2622927​
38.49​
0.83​
$31.95​
9NWDH29PRODUCT01
22/06/2019​
9229193​
34.59​
0.75​
$25.94​
4FEWI57PRODUCT02
3/12/2019​
2522943​
30.89​
0.7​
$21.62​
1WNCF76PRODUCT01
23/10/2019​
6923571​
49.42​
0.91​
$44.97​
2QXHN70PRODUCT01
21/11/2019​
3222919​
34.59​
0.73​
$25.25​
5QPRU63PRODUCT01
28/09/2019​
6122954​
26.56​
0.55​
$14.61​
9PCFF86PRODUCT02
13/04/2019​
4422891​
51.73​
0.71​
$36.73​
9DRPW53PRODUCT01
11/11/2019​
7122891​
40.72​
0.57​
$23.21​
0VIVO22PRODUCT01
22/08/2019​
6422919​
40.72​
0.71​
$28.91​
0KTWO04PRODUCT02
22/08/2019​
4022943​
30.89​
0.13​
$4.02​
8OWCL68PRODUCT01
7/01/2019​
8122954​
26.56​
0.55​
$14.61​
1KNSL89PRODUCT01
31/12/2019​
3122919​
34.59​
0.07​
$2.42​
0CDAL30PRODUCT01
16/06/2019​
7022919​
40.72​
0.71​
$28.91​
 

Attachments

  • 1604904707527.png
    1604904707527.png
    14.8 KB · Views: 14

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Worked it out. I was making it far more complex that it needed to be.
 
Upvote 0

Forum statistics

Threads
1,223,989
Messages
6,175,808
Members
452,670
Latest member
nogarth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top