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

[db042190]

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?

[amitchandak · Accepted solution]

@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

[amitchandak · Accepted solution]

@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

[db042190]

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] ) )

[FreemanZ · Accepted solution]

hi @db042190 

try like:

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

or

jobtotalcost=

SUMX(

    FILTER(

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

     ),

    jobcost[cost]

)

[db042190]

can we mark 2 replies as answers in this forum?  

[FreemanZ]

hi @db042190 

yes