DAX Formula for weighted average price

[pjwhyman]

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_ID	PRODUCT_ID	DATE	EVENT_ID	PRICE	VOLUME	SPEND
6SCEV19	PRODUCT01	27/06/2019​	2229802​	40.72​	0.57​	$23.21​
2ATUV52	PRODUCT01	30/09/2019​	4322943​	50.55​	1.27​	$64.20​
2ZRIW87	PRODUCT01	4/10/2019​	5722919​	40.72​	0.71​	$28.91​
1ATEU55	PRODUCT02	15/07/2019​	8622945​	27.8​	0.13​	$3.61​
5JCON29	PRODUCT01	27/04/2019​	6822919​	34.59​	0.07​	$2.42​
4ILIP37	PRODUCT01	30/08/2019​	7022891​	40.72​	0.71​	$28.91​
1XATY85	PRODUCT01	2/10/2019​	6723571​	49.42​	0.46​	$22.73​
9NYQX23	PRODUCT01	4/05/2019​	5622919​	40.72​	0.71​	$28.91​
3BPNA50	PRODUCT01	6/01/2019​	3122961​	43.25​	0.57​	$24.65​
9FWNT81	PRODUCT01	6/11/2019​	1233555​	50.55​	1.07​	$54.09​
3UJWH64	PRODUCT01	2/02/2019​	9823355​	50.55​	0.38​	$19.21​
6ABSY44	PRODUCT01	26/08/2019​	1522961​	43.25​	0.29​	$12.54​
4EMLC93	PRODUCT01	19/09/2019​	9422954​	26.56​	0.55​	$14.61​
5RVPF51	PRODUCT01	8/09/2019​	8222954​	26.56​	0.55​	$14.61​
2SLYO70	PRODUCT02	25/12/2019​	5229230​	38.48​	1.13​	$43.48​
1PKWO46	PRODUCT01	12/02/2019​	1002291​	34.59​	0.2​	$6.92​
6PQPQ67	PRODUCT01	21/12/2019​	1022919​	34.59​	0.75​	$25.94​
6AOBO07	PRODUCT01	1/10/2019​	1022954​	26.56​	0.28​	$7.44​
9ZDAH66	PRODUCT01	22/11/2019​	3822943​	49.42​	0.06​	$2.97​
9KFOR05	PRODUCT01	4/01/2019​	1235718​	49.42​	0.86​	$42.50​
4BUBX23	PRODUCT01	22/06/2019​	5823355​	50.55​	1.27​	$64.20​
8RRSC80	PRODUCT01	4/03/2019​	2422954​	50.55​	0.76​	$38.42​
2LTLA07	PRODUCT02	17/02/2019​	1229275​	56.47​	0.67​	$37.83​
6IAMG20	PRODUCT01	5/11/2019​	4229578​	34.66​	0.92​	$31.89​
8CIFO28	PRODUCT01	2/12/2019​	6123355​	50.55​	1.27​	$64.20​
9MKOO02	PRODUCT02	9/11/2019​	8822943​	30.89​	0.47​	$14.52​
7VOWM51	PRODUCT01	10/01/2019​	8022919​	40.72​	0.57​	$23.21​
7MGOW86	PRODUCT01	31/08/2019​	1722891​	40.72​	0.43​	$17.51​
5ITKT89	PRODUCT03	14/12/2019​	2422919​	43.92​	0.29​	$12.74​
0WGOS42	PRODUCT01	9/09/2019​	9122891​	40.72​	0.71​	$28.91​
2CUQR99	PRODUCT02	2/02/2019​	3722923​	38.49​	1.43​	$55.04​
4NHNI85	PRODUCT01	7/08/2019​	2322961​	43.25​	0.07​	$3.03​
9EDQV42	PRODUCT01	2/07/2019​	2622927​	38.49​	0.83​	$31.95​
9NWDH29	PRODUCT01	22/06/2019​	9229193​	34.59​	0.75​	$25.94​
4FEWI57	PRODUCT02	3/12/2019​	2522943​	30.89​	0.7​	$21.62​
1WNCF76	PRODUCT01	23/10/2019​	6923571​	49.42​	0.91​	$44.97​
2QXHN70	PRODUCT01	21/11/2019​	3222919​	34.59​	0.73​	$25.25​
5QPRU63	PRODUCT01	28/09/2019​	6122954​	26.56​	0.55​	$14.61​
9PCFF86	PRODUCT02	13/04/2019​	4422891​	51.73​	0.71​	$36.73​
9DRPW53	PRODUCT01	11/11/2019​	7122891​	40.72​	0.57​	$23.21​
0VIVO22	PRODUCT01	22/08/2019​	6422919​	40.72​	0.71​	$28.91​
0KTWO04	PRODUCT02	22/08/2019​	4022943​	30.89​	0.13​	$4.02​
8OWCL68	PRODUCT01	7/01/2019​	8122954​	26.56​	0.55​	$14.61​
1KNSL89	PRODUCT01	31/12/2019​	3122919​	34.59​	0.07​	$2.42​
0CDAL30	PRODUCT01	16/06/2019​	7022919​	40.72​	0.71​	$28.91​

 

[pjwhyman]

Worked it out. I was making it far more complex that it needed to be.