Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
db042190
Responsive Resident
Responsive Resident

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

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?

 

daxfortotalcost.PNG

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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

View solution in original post

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

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@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

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

 

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
Responsive Resident
Responsive Resident

can we mark 2 replies as answers in this forum?  

hi @db042190 

yes

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors