DAX New Column based on values from another column

[adoster]

Hello,

Here is a simplified example of a new column I would like to create.

On TABLE_A I would like to create a New column that shows the difference between the Budget and the AVERAGE cost by Activity

TABLE_A

Person	Activity	Cost	Budget
John	Biking	10	15
Laura	Camping	20	25
Michael	Hiking	30	35
Adam	Biking	20	25
Laura	Camping	30	35
Michael	Hiking	40	45
John	Biking	30	30
Rachel	Camping	40	40
Michael	Hiking	50	50

END RESULT

would either have 2 new column or just the Difference

Person	Activity	Cost	Budget	AVGCost of Activity	Difference b/t Budget & AVGCost
John	Biking	10	15	18.75	-3.75
Laura	Camping	20	25	28.75	-3.75
Michael	Hiking	30	35	38.75	-3.75
Adam	Biking	20	25	18.75	6.25
Laura	Camping	30	35	28.75	6.25
Michael	Hiking	40	45	38.75	6.25
John	Biking	30	30	18.75	11.25
Rachel	Camping	40	40	28.75	11.25
Michael	Hiking	50	50	38.75	11.25

[v-yueyunzh-msft · Accepted solution]

Hi , @adoster 

According to your description, you want to add two columns to calculate the " AVERAGE cost by Activity" and the "Differences betwwen budget and AVERAGE cost by Activity".

For your test data , in the [Activity]="Biking" , the [AVERAGE cost by Activity] should = (10+20+30)/3 =20.

But in your expected table , the number is not right i think.

Here are the steps you can refer to :
(1)You can click "New Column" and enter:

AVGCost of Activity = var _activity= [Activity]
var _t =FILTER('Table','Table'[Activity]=_activity)
return
AVERAGEX(_t, [Cost])

Difference b/t Budget & AVGCost = [Budget]-[AVGCost of Activity]

(2)Then we can get your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@adoster ,

A new column

averagex(Filter(Table_A, [Activity] = earlier([Activity]) ), [Cost])

a new  measure

averagex(Filter(allselected(Table_A), [Activity] = Max([Activity]) ), [Cost])