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

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!

 

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.