cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Voltzs Frequent Visitor
Frequent Visitor

Calculate the average of values across several columns in same row

I'm trying to calculate the average across serveral columns of data but a single row, but the examples i've looked it it only involves columns

 

 Capture.PNG

 

So for above i would like the average for the country of Albania from 1995-2000 and the result shown in a seperate column called Average by Country. Been a novice to Power BI i thought this could be achieved using Quick Measure.

 

Why can't you select rows in Power BI and only Columns???. I was thinking you could select a row then use New Quick Measure

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
affan Established Member
Established Member

Re: Calculate the average of values across several columns in same row

Hi @Voltzs

 

You can create a calculated column

Average by Country = VAR _Total=[1995]+[1996]+[1997]+[1998]+[1999]+[2000]
var _avg= _Total/6
return 
_avg

 

You ca find the sample pbix file here

 

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

 

View solution in original post

4 REPLIES 4
Highlighted
Super User III
Super User III

Re: Calculate the average of values across several columns in same row

Hi @Voltzs

 

I think it would be best to restructure the data in your table. Use the Pivot/Unpivot feature inthe query editor to get the table in the form shown below. Then you can do what you are looking for in a much more convenient way. 

 

Country          Code      Year         Value

Albania            ALB       1995        304000

Albania            ALB       1996        287000

Albania            ALB       1997        119000

...

Afghanistan     AFG       1995       

 

affan Established Member
Established Member

Re: Calculate the average of values across several columns in same row

Hi @Voltzs

 

You can create a calculated column

Average by Country = VAR _Total=[1995]+[1996]+[1997]+[1998]+[1999]+[2000]
var _avg= _Total/6
return 
_avg

 

You ca find the sample pbix file here

 

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

 

View solution in original post

Community Support Team
Community Support Team

Re: Calculate the average of values across several columns in same row

Hi @Voltzs,

 

By my tests, both of solution from affan and AlB should be helpful. If you will have more column for year, the solution of AlB will be more simpler.

 

If you use the solution from AlB, you could create the measure with the formula below.

 

Measure =
CALCULATE (
    AVERAGE ( Table2[Value] ),
    ALLEXCEPT ( Table2, Table2[Country Name], Table2[Country Code] )
)

In addition, for your scenario, if you want to unpivot columns for the year columns, please replace the null value to 0, otherwise it will ignore the null value after unpivot columns.

 

Capture.PNG

 

If you have solved your problem, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please feel free to ask.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Voltzs Frequent Visitor
Frequent Visitor

Re: Calculate the average of values across several columns in same row

Its clear i need to scrub up on the power of DAX

 

After Unpivot the table as @AlB suggested I used the measure @v-piga-msft suggested. My version below

 

Measure = CALCULATE ( AVERAGE('Tourism Numbers'[Value] ),ALLEXCEPT('Tourism Numbers','Tourism Numbers'[Country Name],'Tourism Numbers'[Country Code]))
 
i get the warning: The AVERAGE function only accepts a column reference as an argument.
 
In this instance I can confirm @affan method is indeed the quickest way to solve this solution, but none the less i'm intrested in getting the Unpivot Scenario working as it seems for want of a better word cleaner should more data be added at a later date

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors