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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.