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
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 |