cancel
Showing results for
Did you mean:
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

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

4 REPLIES 4
Super User

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

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

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.

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.

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

Announcements