topic Re: Calculate difference in cost based on combined query results in DAX Commands and Tips

Calculate difference in cost based on combined query results

Mido99 — Mon, 26 Sep 2022 16:58:00 GMT

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

Re: Calculate difference in cost based on combined query results

amitchandak — Tue, 27 Sep 2022 02:18:41 GMT

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 )

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

Re: Calculate difference in cost based on combined query results

v-xiaotang — Tue, 27 Sep 2022 02:29:05 GMT

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.