Calculate difference in cost based on combined query results

[Mido99]

hi all,

i have a table in Power BI that is the result of a combined query (3 tables), which means i have a column with 3 different project IDs (4010 = current project, 3844 = previous month's project, 3653 = baseline)

i have another column called "actual cost" and what i would like to do is add a calculated column that calculates for each row the difference between the actual cost in ID 4010 vs ID 3844, based on the denominator activity ID or name.

how would i go about doing this?

thank you in advance

[v-xiaotang · Accepted solution]

Hi @Mido99 

Thanks for reaching out to us.

My suggestion is to find the values where project ID=4010 and 3653 by denominator activity ID, and then calculate the difference. For example,

sample data

Column = 
var _4010=MAXX(FILTER(ALL('Table'),'Table'[denominator activity ID]=EARLIER('Table'[denominator activity ID]) && [project IDs]=4010),[actual cost])
var _3844=MAXX(FILTER(ALL('Table'),'Table'[denominator activity ID]=EARLIER('Table'[denominator activity ID]) && [project IDs]=3844),[actual cost])
return _3844-_4010

result

Best Regards,

Community Support Team _Tang

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

[amitchandak]

@Mido99 , if you need a column

Cost 4010   =

sumx(filter(Table, Table[Project ID]= 4010  && Table[Activity ID] = earlier(Table[Activity ID] ) ), Table[Cost])

cost 3844 =

sumx(filter(Table, Table[Project ID]= 3844 && Table[Activity ID] = earlier(Table[Activity ID] ) ), Table[Cost])

if you need measure

calculate(sum(Table[Cost]), Table[Project ID]= 3844 )

or

calculate(sum(Table[Cost]), Table[Project ID]= 4010)