Forum Discussion

mweber87's avatar
mweber87
Helper I
3 years ago

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 w...
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
mweber87
Helper I
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      mweber87
      Helper I
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        Hello mweber87 can you please explain it more