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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
mweber87
Helper I
Helper I

Column Filter

I have a labor cost table. One of the columns is job_id. In this column there are 5259 distinct values. And each job_id has multiple entries (rows) with data. I am trying to get the labor total per week per job id. Is there a way to group the identical job_id so that I can sum the total of each. I know I could use DAX filter and filter out each job_id individually. However since there are so many records is there an easier time saving way to do this?

 

mweber87_0-1667235647369.png

 

8 REPLIES 8
Anonymous
Not applicable

hello you could try and use the allexcept(table,table[job_id])

I think I may have worded my question wrong. I do not want to get rid of the whole column. I want to some how be able to add up the transaction total for each job id per week. Each job_id has multiple entries.

 

Thanks!

mweber87_0-1667235527401.png

 

Anonymous
Not applicable

ok can you please sample the data?
and i didnt quite understand your question do you mean you want to count the job id per week?

Here is a sample set: This one JOB_ID has muliple Transaction_Amount entries. I want to be able to sum all of the transaction_amounts for each Job_ID. There are about 5000 different JOB_ID. My end result is to calcualte the labor per week per job id.

 

Transaction_DateJOB_IDETYPETransaction_Amount
Thursday, August 02, 201216246LAB878.61
Thursday, August 09, 201216246LAB2912.62
Thursday, August 16, 201216246LAB6222.66
Thursday, August 23, 201216246LAB5111.06
Thursday, August 30, 201216246LAB3163.87
Thursday, September 06, 201216246LAB2085.32
Thursday, September 13, 201216246LAB1652.74
Thursday, September 20, 201216246LAB2092.15
Thursday, September 27, 201216246LAB2142.2
Thursday, October 04, 201216246LAB2313.74
Thursday, October 11, 201216246LAB1821.57
Anonymous
Not applicable

hello @mweber87 ,
please give this a try:

 

total = CALCULATE(SUM(powerbi[Transaction_Amount]),ALLEXCEPT(powerbi,powerbi[JOB_ID],powerbi[week]))

 

Screenshot 2022-11-03 020036.png

 

if this helps, kindly accept as solution

Yes that does work however there are 5,259 different job_id's that I need to total. And I was hoping there was a way I could group the job id's without having to write an expression for each job id.

Anonymous
Not applicable

Hello @mweber87 can you please explain it more

The data set contains a job id, this job has a labor cost weekly until the job is finished (contruction work) There are 5,259 different jobs and each job has a labor cost assoicated with it each week. I need to figure out how much each job's labor cost is per week. Ultimately, I have to create a visual that shows the labor cost per week per job for the life of the job. 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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