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

[iamprajot]

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

[Greg_Deckler · Accepted solution]

Create a column with the following formula:

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

[v-caliao-msft · Accepted solution]

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

Regards,

Charlie Liao

[bsas]

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 · Accepted solution]

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

Regards,

Charlie Liao

[iamprajot]

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)

[Greg_Deckler · Accepted solution]

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?

[iamprajot]

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 · Accepted solution]

Create a column with the following formula:

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

[iamprajot]

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.

[Greg_Deckler]

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.

[iamprajot]

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