Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
Create a column with the following formula:
Average Price = ([Price 1] + [Price 2] + [Price 3]) / 3
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
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?
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.
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
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?
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.
Create a column with the following formula:
Average Price = ([Price 1] + [Price 2] + [Price 3]) / 3
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.
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].
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |