cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ak_rafa
Regular Visitor

Calculation of averages using data from two different columns as filters.

Afternoon,

I have 3 columns, in first - different products, in second - country of origin, in third - wieght of the product (number).

 

Is it possible to calculate average value of each product for each country separately?

Something like this (in last column is what I want to achieve):

 

Fruit_typeCountryweightArith_weight_average_by_fruit_type_and_country
applesSpain23
applesSpain33
applesSpain43
grapesItaly11.5
grapesItaly21.5
applesNorway33.5
applesNorway43.5
pineapplePeru55.33
pineapplePeru65.33
pineapplePeru55.33

Any hints / Tips apreciated, thanks

1 ACCEPTED SOLUTION
ERD
Super User
Super User

Hi @ak_rafa ,

You can use this measure:

 

AvgW =
VAR currentFruit = SELECTEDVALUE ( T[Fruit_type] )
VAR currentCountry = SELECTEDVALUE ( T[Country] )
RETURN
    AVERAGEX (
        FILTER (
            ALL ( T ),
            T[Fruit_type] = currentFruit && T[Country] = currentCountry
        ),
        T[weight]
    )

ERD_0-1632482096192.png

 

 

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi @ak_rafa,

Did the above suggestions help with your requirement? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
ERD
Super User
Super User

Hi @ak_rafa ,

You can use this measure:

 

AvgW =
VAR currentFruit = SELECTEDVALUE ( T[Fruit_type] )
VAR currentCountry = SELECTEDVALUE ( T[Country] )
RETURN
    AVERAGEX (
        FILTER (
            ALL ( T ),
            T[Fruit_type] = currentFruit && T[Country] = currentCountry
        ),
        T[weight]
    )

ERD_0-1632482096192.png

 

 

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

View solution in original post

amitchandak
Super User
Super User

@ak_rafa , a new column

 

col = averagex(filter(Table, [Fruit_type] = earlier([Fruit_type] )) , [weight])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@amitchandak  many thanks for helping me out. It solves half of my issue. 

By using the expression that you have provided i got average weight by fruit_type. But it has averaged the fruits from different country. 
Making it short - i need to apply 2 filters: by fruit type and by country.

Any clues how it can be done?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.