cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndresOHV
Frequent Visitor

Getting an average purchased price

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.

 

AndresOHV_1-1666299369072.png

 

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

 

AndresOHV_2-1666299482786.png

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?

 

1 ACCEPTED SOLUTION
eliasayy
Impactful Individual
Impactful Individual

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:

Screenshot 2022-10-21 031909.png

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

View solution in original post

6 REPLIES 6
eliasayy
Impactful Individual
Impactful Individual

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

Help.png



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.

 

 

DateProduct IdPurchase qtyPriceTotal QTYAverage Price
30/12/2021 00:00149276179.71028180
31/12/2021 00:00149471177.571028178
31/12/2021 00:00149281177.571028178
4/1/2022 00:001490179.71028180
7/1/2022 00:00149557172.171028172
7/1/2022 00:00149-557172.171028172
12/1/2022 00:00149-557010280
12/1/2022 00:00149557010280
eliasayy
Impactful Individual
Impactful Individual

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:

Screenshot 2022-10-21 031909.png

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

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

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors