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.

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.

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

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

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

6 REPLIES 6
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))``

Frequent Visitor

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.

 Date Product Id Purchase qty Price Total QTY Average Price 30/12/2021 00:00 149 276 179.7 1028 180 31/12/2021 00:00 149 471 177.57 1028 178 31/12/2021 00:00 149 281 177.57 1028 178 4/1/2022 00:00 149 0 179.7 1028 180 7/1/2022 00:00 149 557 172.17 1028 172 7/1/2022 00:00 149 -557 172.17 1028 172 12/1/2022 00:00 149 -557 0 1028 0 12/1/2022 00:00 149 557 0 1028 0
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:

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

Frequent Visitor

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

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

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors