topic Re: Average Purchase Price in every instant in DAX Commands and Tips

Average Purchase Price in every instant

AndresOHV — Wed, 26 Oct 2022 18:08:17 GMT

Hello All!

I'm working with a fact table and need to get the average purchase price for every instant.

What I need to do is multiply the price of every purchase for the quantity and divide that result in the accumulated quantity considering the same for the previous purchases, as the excel example below:

Date	ProductID	Operation	Qty	Accumulated Qty	Price	AVG Price	Example
29/12/2021 00:00	1255	Buy	7	7	$ 3.251,00	$ 3.251,00	7*3251/7
31/12/2021 00:00	1255	Buy	3	10	$ 3.334,00	$ 3.275,90	33334/10 + 73251/10
7/1/2022 00:00	1255	Buy	7	17	$ 3.251,00	$ 3.265,65	73251/17 + 33334/17 + 7*3251/17
11/1/2022 00:00	1255	Buy	7	25	$ 3.307,00	$ 3.146,60	73307/25 + 73251/25 + 33334/25 + 73251/25
19/1/2022 00:00	1255	Sell	-25	0	$ 3.125,00

I wasn't able to replicate this in PBI, can anyone help me with this?

Re: Average Purchase Price in every instant

daXtreme — Wed, 26 Oct 2022 18:57:07 GMT

OK. This is rather simple and can be easily done in Power Query. But showing only one "Sell" entry and with 0 at that... does not help. You have to provide an example that's REPRESENTATIVE.

Re: Average Purchase Price in every instant

tamerj1 — Wed, 26 Oct 2022 19:06:02 GMT

Hi AndresOHV
Please refer to attached sample file with the solution

AVG Price = VAR CurrentOperation = 'Table'[Operation] VAR CurrentDate = 'Table'[Date] VAR CurrentAccQty = 'Table'[Accumulated Qty] VAR CurrentIDTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ProductID] ) ) VAR BuyOnAndBefore = FILTER ( CurrentIDTable, 'Table'[Date] <= CurrentDate && 'Table'[Operation] = "Buy" ) RETURN IF ( CurrentOperation = "Buy", DIVIDE ( SUMX ( BuyOnAndBefore, [Qty] * 'Table'[Price] ), CurrentAccQty ) )

Re: Average Purchase Price in every instant

AndresOHV — Wed, 26 Oct 2022 22:07:59 GMT

Hi tamerj1 ! thank you for your reply.

I'm trying to replicate this buy I'm having a "circular dependency" when creating the column.

The productId and the date come from 2 different dimension tables. Wouldn't be better to create a measure instead of a column?

Re: Average Purchase Price in every instant

v-yueyunzh-msft — Thu, 27 Oct 2022 02:59:32 GMT

Hi , AndresOHV

For measure , you can use this dax :

Measure = var _date =SELECTEDVALUE('Sheet1'[Date]) var _product_id = SELECTEDVALUE('Sheet1'[ProductID]) var _operation =MAX('Sheet1'[Operation]) var _acc_qty = MAX('Sheet1'[Accumulated Qty]) var _t = FILTER( ALLSELECTED( 'Sheet1') , 'Sheet1'[ProductID] =_product_id && 'Sheet1'[Date] <= _date && 'Sheet1'[Operation] = "Buy" ) return DIVIDE(SUMX(_t , [Price] * [Qty]),_acc_qty)

The result is as follows:

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Average Purchase Price in every instant

tamerj1 — Thu, 27 Oct 2022 09:52:31 GMT

Hi AndresOHV
Still not very clear, therefore, I made some assumptions again.

AVG Price = VAR CurrentDate = MAX ( 'Date'[Date] ) VAR CurrentOperation = SELECTEDVALUE ( Sales[Operation] ) VAR AccumulatedQty = [Accumulated Qty] VAR T1 = CALCULATETABLE ( SUMMARIZE ( Sales, Sales[ProductID], Sales[Price], Sales[Operation], 'Date'[Date] ), REMOVEFILTERS ( ), VALUES ( 'Sales'[ProductID] ), 'Sales'[Operation] = "Buy" , 'Date'[Date] <= CurrentDate ) VAR T2 = ADDCOLUMNS ( T1, "@Qty", [Qty] ) RETURN IF ( CurrentOperation = "Buy", DIVIDE ( SUMX ( T2, [@Qty] * [Price] ), AccumulatedQty ) )

Re: Average Purchase Price in every instant

AndresOHV — Thu, 27 Oct 2022 15:29:13 GMT

Hi v-yueyunzh-msft

I'm trying to use this but I get a different result. I think it's because you're using just one table and in the real model I have the date and product id in different tabels so I'm not sure how to make it work.

Re: Average Purchase Price in every instant

v-yueyunzh-msft — Fri, 28 Oct 2022 02:10:13 GMT

Hi , AndresOHV

You are right , the measure need to write by the table relationship. So do you want to share the sample .pbix file to me so that we can help you better.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Average Purchase Price in every instant

AndresOHV — Fri, 28 Oct 2022 16:15:31 GMT

Hi v-yueyunzh-msft ! Thank you for your help.

I'm copying a link to the pbi file:

https://1drv.ms/u/s!AvSFZ2hooRIDgdJPNOk7duNVPTrnbQ?e=STE3Xr

Re: Average Purchase Price in every instant

tamerj1 — Fri, 28 Oct 2022 19:32:08 GMT

Hi AndresOHV
Please refer to attched sample file with the proposed solution. Please let me know if this is as per your requirement or something else is required.

Acc QTY = CALCULATE ( [Qty], FILTER ( ALL ( 'Calendar' ), 'Calendar'[Fecha] <= MAX ( 'Calendar'[Fecha] ) ), ALL ( FactExample[Operation] ), ALL ( FactExample[Price] ) )

AVG Price = VAR CurrentDate = MAX ( 'Calendar'[Fecha] ) VAR CurrentOperation = SELECTEDVALUE ( FactExample[Operation] ) VAR AccumulatedQty = [Acc QTY] VAR T1 = CALCULATETABLE ( SUMMARIZE ( FactExample, Ticker[Ticker], 'Calendar'[Fecha], FactExample[Price], FactExample[Operation] ), REMOVEFILTERS ( ), VALUES ( FactExample[Ticker] ), FactExample[Operation] <> "Sell" , 'Calendar'[Fecha] <= CurrentDate ) VAR T2 = ADDCOLUMNS ( T1, "@Qty", [Qty] ) RETURN IF ( CurrentOperation <> "Sell", DIVIDE ( SUMX ( T2, [@Qty] * [Price] ), AccumulatedQty ) )

Re: Average Purchase Price in every instant

AndresOHV — Sun, 30 Oct 2022 22:53:13 GMT

This worked tamerj1 ! Thank you for your help.

Do you know how can I complete this measure so in the case of a Sell Operation rest the selling price to the calculated average?