Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors