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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.