cancel
Showing results for
Did you mean:
Responsive Resident

## How to calculate Average of 3 Columns and put in 4th Column using Calculated Column.

How to calculate Average of 3 Columns and put in 4th Column using Calculated Column.

 Product Price 1 Price 2 Price 3 Average Price A 3 3 1 2.3 B 5 1 3 3.0 C 2 5 5 4.0
3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User IV

## Re: How to calculate Average of 3 Columns and put in 4th Column using Calculated Column.

Create a column with the following formula:

`Average Price = ([Price 1] + [Price 2] + [Price 3]) / 3`

---------------------------------------

Not the Power BI thought police...

##### I have NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Microsoft

## Re: How to calculate Average of 3 Columns and put in 4th Column using Calculated Column.

Please use the DAX expression below.
Column =
var countvalue = (3-ISBLANK(Sales[Price1])-ISBLANK(Sales[Price2])-ISBLANK(Sales[Price3]))
return
IF(countvalue=0,BLANK(),(Sales[Price1]+Sales[Price2]+Sales[Price3])/countvalue)

Regards,

Charlie Liao

Super User IV

## Re: How to calculate Average of 3 Columns and put in 4th Column using Calculated Column.

I just used @v-caliao-msft formula in a calculated column and did not get a circular dependency. Not sure what in that formual could cause that. Do you get a circular dependency with the measure formula that I provided?

---------------------------------------

Not the Power BI thought police...

##### I have NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

9 REPLIES 9
Super User IV

## Re: How to calculate Average of 3 Columns and put in 4th Column using Calculated Column.

Create a column with the following formula:

`Average Price = ([Price 1] + [Price 2] + [Price 3]) / 3`

---------------------------------------

Not the Power BI thought police...

##### I have NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Responsive Resident

## Re: How to calculate Average of 3 Columns and put in 4th Column using Calculated Column.

Well, this is the last option I have in mind.

I was more looking for something like using functions of Power BI as I have blank values in it so dividing the SUM with 3 always is not gonna be a permanent solution.

Super User IV

## Re: How to calculate Average of 3 Columns and put in 4th Column using Calculated Column.

While there is probably a way, it's not straight-forward to UNION several columns toegether. I tried messing around with SUMMARIZECOLUMNS, UNION, VALUES, etc. and couldn't really come up with a way. The closest that I got was this:

`Column 2 = AVERAGEX(UNION(SELECTCOLUMNS(Table1,"Column",Table1[Price 1]),SELECTCOLUMNS(Table1,"Column",Table1[Price 2]),SELECTCOLUMNS(Table1,"Column",Table1[Price 3])),[Column])`

But the row context is not preserved and this gets all of the values in the 3 columns. But, a Measure created in this way and placed into a visual with [Product] column does seem to work like a champ.

---------------------------------------

Not the Power BI thought police...

##### I have NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Microsoft

## Re: How to calculate Average of 3 Columns and put in 4th Column using Calculated Column.

Please use the DAX expression below.
Column =
var countvalue = (3-ISBLANK(Sales[Price1])-ISBLANK(Sales[Price2])-ISBLANK(Sales[Price3]))
return
IF(countvalue=0,BLANK(),(Sales[Price1]+Sales[Price2]+Sales[Price3])/countvalue)

Regards,

Charlie Liao

Responsive Resident

## Re: How to calculate Average of 3 Columns and put in 4th Column using Calculated Column.

Hi,

Thanks for the time.

I tried but somehow it shows, A circular dependency was detected: Table1[Price 1], Table1[Column 2], Table1[Price 1].

Responsive Resident

## Re: How to calculate Average of 3 Columns and put in 4th Column using Calculated Column.

Hi, Thanks

I am getting, A circular dependency was detected: Sales[Price 1], Sales[Price Avg], Sales[Price 1].

Price Avg =

var countvalue = (3-ISBLANK(Sales[Price1])-ISBLANK(Sales[Price2])-ISBLANK(Sales[Price3]))
return
IF(countvalue=0,BLANK(),(Sales[Price1]+Sales[Price2]+Sales[Price3])/countvalue)

Super User IV

## Re: How to calculate Average of 3 Columns and put in 4th Column using Calculated Column.

I just used @v-caliao-msft formula in a calculated column and did not get a circular dependency. Not sure what in that formual could cause that. Do you get a circular dependency with the measure formula that I provided?

---------------------------------------

Not the Power BI thought police...

##### I have NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Helper V

## Re: How to calculate Average of 3 Columns and put in 4th Column using Calculated Column.

Hi,

This is pure logic, but maybe will be usefull.

`Average = IFERROR((Table1[Price1]+Table1[Price2]+Table1[price3])/(IF(Table1[Price1]>0,1,0)+IF(Table1[Price2]>0,1,0)+IF(Table1[price3]>0,1,0)),BLANK())`

If you need different than "blank" result (in case of 0 in all price rows) just change expression iferror in formula.

Responsive Resident

## Re: How to calculate Average of 3 Columns and put in 4th Column using Calculated Column.

I think there is something else wrong with mine data as I am getting a lot of circular dependency on almost everything I do.

But thanks anyway for all your help, this community is really something, doesn't it.

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors