topic Re: what if i want a new column dax formula to sum only on a specific condition in DAX Commands and Tips

what if i want a new column dax formula to sum only on a specific condition

db042190 — Fri, 28 Apr 2023 20:17:49 GMT

hi we are just getting started wth pbi and you can see below i've created a new column using dax that is the sum of job cost. What if i want it to sum only when the job number is the same as the job number on this row?

Re: what if i want a new column dax formula to sum only on a specific condition

amitchandak — Fri, 28 Apr 2023 20:37:28 GMT

db042190 , All jobnumber seems the same, But you can use earlier to get that

Sumx(filter(jobs, Jobs[jobnumber] = earlier( Jobs[jobnumber]) ), Jobs[Quantity])

Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s

Re: what if i want a new column dax formula to sum only on a specific condition

db042190 — Fri, 28 Apr 2023 20:47:34 GMT

thx, is this also an option? What if both job number and item id have to match?

jobtotalcost = CALCULATE( SUM( jobcost[cost] ), ALLEXCEPT( jobcost, jobcost[jobnumber] ) )

Re: what if i want a new column dax formula to sum only on a specific condition

FreemanZ — Fri, 28 Apr 2023 22:45:36 GMT

hi db042190

try like:

jobtotalcost = CALCULATE( SUM( jobcost[cost] ), ALLEXCEPT( jobcost, jobcost[jobnumber], jobcost[itemnumber]))

jobtotalcost=

SUMX(

FILTER(

jobcost, jobcost[jobnumber]=EARLIER(jobcost[jobnumber])&&jobcost[itemnumber]=EARLIER(jobcost[itemnumber])

jobcost[cost]

)

Re: what if i want a new column dax formula to sum only on a specific condition

db042190 — Mon, 01 May 2023 12:28:27 GMT

can we mark 2 replies as answers in this forum?

Re: what if i want a new column dax formula to sum only on a specific condition

FreemanZ — Wed, 03 May 2023 02:21:29 GMT

hi db042190

yes