topic Re: Getting an average purchased price in DAX Commands and Tips

Getting an average purchased price

AndresOHV — Thu, 20 Oct 2022 20:59:49 GMT

Hello all!

I'm having a problem with a measure. Total QTY is ok, I want it to show the total purchased regardless the filter context.

The problem is that I try to get the average price this way:

As you can see in the table, the measure it's not dividing by the total quantity (1028), instead it's doing it by the normal quantity.

Can anyone help me with this?

Re: Getting an average purchased price

Greg_Deckler — Thu, 20 Oct 2022 21:03:53 GMT

AndresOHV Perhaps try CALCULATE([Total Qty], ALL('Table')

Re: Getting an average purchased price

AndresOHV — Thu, 20 Oct 2022 23:37:57 GMT

Hi Greg! Thank you for your answer.

That's not working, I think because I need to keep the filter of a specific product, using ALL removes that filter.

Re: Getting an average purchased price

Anonymous — Thu, 20 Oct 2022 23:40:49 GMT

hello AndresOHV ,
please try the below im not sure if i understood you correctly but pleaae try:
create 2 measures first one is:

Total QTY = CALCULATE(SUM(HelpBi[Purchase Qtty]),ALL(HelpBi))

the second one is

Average Price = SUMX(HelpBi,DIVIDE((HelpBi[Purchase Qtty]*HelpBi[Price]),[Total QTY],0))

Re: Getting an average purchased price

AndresOHV — Thu, 20 Oct 2022 23:59:08 GMT

Hi Anonymous !

I'm copying the table below with an extra column (productId).

The problem of using ALL is that it removes the product filter and I need the measure to calculate the average price for the selected product so using ALL is not working.

Date	Product Id	Purchase qty	Price	Total QTY	Average Price
30/12/2021 00:00	149	276	179.7	1028	180
31/12/2021 00:00	149	471	177.57	1028	178
31/12/2021 00:00	149	281	177.57	1028	178
4/1/2022 00:00	149	0	179.7	1028	180
7/1/2022 00:00	149	557	172.17	1028	172
7/1/2022 00:00	149	-557	172.17	1028	172
12/1/2022 00:00	149	-557	0	1028	0
12/1/2022 00:00	149	557	0	1028	0

Re: Getting an average purchased price

Anonymous — Fri, 21 Oct 2022 00:20:32 GMT

hello thank you for providing details,
kidly change the first measure i provided to :

total qtty = CALCULATE(SUM(HelpBi[Purchase Qtty]),ALL(HelpBi),HelpBi[ID]=MAX(HelpBi[ID]))

i added some sample to show you how it work:

please tell me if it works if it does please accept as solution to help other users

Re: Getting an average purchased price

AndresOHV — Fri, 21 Oct 2022 00:26:29 GMT

Thank you so much Anonymous ! This was exactly what I was needing! 😀