Getting an average purchased price

[AndresOHV]

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?

[eliasayy]

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

 

[AndresOHV]

Hi @eliasayy !

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

[eliasayy · Accepted solution]

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

[AndresOHV]

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

[Greg_Deckler]

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

[AndresOHV]

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.