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
Super User IV

Create a column with the following formula:

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

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

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

Proud to be a Super User!

Microsoft

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

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?

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

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

Proud to be a Super User!

9 REPLIES 9
Post Patron

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.

Microsoft

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

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

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?

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

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

Proud to be a Super User!

Responsive Resident

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.

Super User IV

Create a column with the following formula:

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

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

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

Proud to be a Super User!

Responsive Resident

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

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.

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

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

Proud to be a Super User!

Responsive Resident

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

Announcements