cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iamprajot
Responsive Resident
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.

ProductPrice 1Price 2Price 3Average Price
A3312.3
B5133.0
C2554.0
3 ACCEPTED SOLUTIONS
Greg_Deckler
Super User IV
Super User IV

Create a column with the following formula:

 

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

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

v-caliao-msft
Microsoft
Microsoft

@iamprajot,

 

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)

Capture.PNG

 

Regards,

Charlie Liao

 

View solution in original post

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?


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

9 REPLIES 9
bsas
Post Patron
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.

v-caliao-msft
Microsoft
Microsoft

@iamprajot,

 

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)

Capture.PNG

 

Regards,

Charlie Liao

 

View solution in original post

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)

 

 

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?


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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.

Greg_Deckler
Super User IV
Super User IV

Create a column with the following formula:

 

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

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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.

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.


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors