Column Filter

[mweber87]

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?

[eliasayy]

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

[mweber87]

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!

[eliasayy]

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?

[mweber87]

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_Date	JOB_ID	ETYPE	Transaction_Amount
Thursday, August 02, 2012	16246	LAB	878.61
Thursday, August 09, 2012	16246	LAB	2912.62
Thursday, August 16, 2012	16246	LAB	6222.66
Thursday, August 23, 2012	16246	LAB	5111.06
Thursday, August 30, 2012	16246	LAB	3163.87
Thursday, September 06, 2012	16246	LAB	2085.32
Thursday, September 13, 2012	16246	LAB	1652.74
Thursday, September 20, 2012	16246	LAB	2092.15
Thursday, September 27, 2012	16246	LAB	2142.2
Thursday, October 04, 2012	16246	LAB	2313.74
Thursday, October 11, 2012	16246	LAB	1821.57

[eliasayy]

hello @mweber87 ,
please give this a try:

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

 

if this helps, kindly accept as solution

[mweber87]

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.

[eliasayy]

Hello @mweber87 can you please explain it more

[mweber87]

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.