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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors