help summing two categories in a variable with corresponding values

[Aj24]

Hi Friends!

Just a quick question for summing two values.

My goal is: to create total cost value.

usually to create a new column we use: 

column = table[variable] + table[variable]

but this gets slightly tricky with my data. 

Current data is like this:

table called 'costs' and below are some variables in the table:

date	type	value	uniqueID
1/5/2020	expenditure	$10	1
1/7/2020	forecast	$20	1
1/6/2020	expenditure	$50	2
1/8/2020	forecast	$20	2
1/4/2020	expenditure	$20	1

so I want to create a new measure called total cost where each value which is expenditure is summed with each value that is a forecast for its relevant corresponding uniqueID.

so far I have: 

total cost = costs[type] = "expenditure" + cost[type] = "forecast"

but I don't know where each corresponding of the values comes in to be summed together... any help much appreciated!!

thanks in advance for all the shared tips and advice!! 

[v-robertq-msft · Accepted solution]

Hi, @Aj24 

According to your description and sample data, I think you can try this measure to achieve your requirement:

total cost =

CALCULATE(

    SUM('costs'[value]),

    FILTER(ALLSELECTED('costs'),

    [type] in {"expenditure","forecast"}&&

    [UniqueID]=MAX([UniqueID])))

Then create a table chart and place the column and measure like this:

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[amitchandak]

@Aj24 , Create a new column like

sumx(filter(Table, [type] =earlier([type])), [value])

[Aj24]

Thanks so much for your guidance @amitchandak !! 

I wrote it as: 

sumx(filter(Costs, Expenditure =earlier(Forecast)), Value) but unfortunately it didn't work 😞 

The error appeared saying 'the syntax for ')' is incorrect 

Really appreciate your time and help!!