Forum Discussion

SoulCuber's avatar
SoulCuber
Frequent Visitor
9 years ago
Solved

Calculate column with subtotals

I would like to create a column/measure that computes the sum of one column for all distinct combinations of two other columns.   The example below shows hours per operator per date. The last colum...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    hello SoulCuber

     

    This give you the solution

     

    Job Hours = CALCULATE(Sum(Table1[Hours]),ALLEXCEPT(Table1,Table1[Date],Table1[Job]))

     

    =)

DSimma's avatar
DSimma
Icon for Advocate IV rankAdvocate IV
9 years ago

You can use the summarize function to create a new table from this table that is summarized on the first two columns with a sum of whatever other columns you want, or other functions.

 

ie somthing like table 2=summarize(table1, date, job, "Total Job Hours", Sum(Job Hours)

  • SoulCuber's avatar
    SoulCuber
    Frequent Visitor
    9 years ago

    Thanks a lot DSimma for your suggestion. That does indeed create the desired column, but in a different table. Which is indeed what I have done now. However, I would like to have the column in the original table. I guess I could link the summed column back into the original column by creating a new key column, but that is ugly. I wondered if there would be a formula for it.