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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mweber87
Helper I
Helper I

Sum parts of a column based on a different column

I have a data set that has a job_id column and a transaction_amount column. The transaction amount is related to a specific date. The job_id column has over 5000 individual job_id's. Each job_id shows up multiple times. I am trying to sum the transaction amount per job id. I know I could filter out each one individually. However, I want to create a column or a measure that would give me the sum of the entire life of the job_id.

mweber87_0-1667838271848.png

 

3 ACCEPTED SOLUTIONS

@mweber87 
This is exactly what this code should give you. Are you sure this the grand total amount or this is just the total amount of the 300 transactions of Job_ID 16246? Would you please paste a screenshot of the code. Otherwise please try

Total Job Tranactions =
CALCULATE (
    SUM ( 'Table'[Transaction_Amount] ),
    ALLEXCEPT ( 'Table', 'Table'[Job_ID] )
)

 

View solution in original post

@tamerj1 the last code you sent worked! Thank you!!!

View solution in original post

@mweber87 
Please try

Rolling Total by Job Id =
VAR CurrentDate = d[transaction_date]
VAR CurrentJobTable =
    CALCULATETABLE ( d, ALLEXCEPT ( d, d[Job_Id] ) )
VAR Filtered =
    FILTER ( d, d[Transaction_date] <= CurrentDate )
RETURN
    SUMX ( Filtered, d[transaction_amount] )

View solution in original post

15 REPLIES 15
tamerj1
Super User
Super User

Hi @mweber87 
Please try

Total Job Tranactions =
CALCULATE (
    SUM ( 'Table'[Transaction_Amount] ),
    REMOVEFILTERS (),
    VALUES ( 'Table'[Job_ID] )
)

@tamerj1 ,

thank you that gives me a column with the total transactions for all the jobs, I need it to be per job_id. There are over 5000 job id's but over 100,000 transaction amounts

 

mweber87_0-1667840345022.png

 

@mweber87 
I can see different values. What exactly do you mean by "that gives me a column with the total transactions for all the jobs"? Actually this code should give you the total value for each each job for all dates.

@tamerj1 ,

 

It gives me the same number for every row. I need the total transaction amount for all job_ids. So for example job_id 16246 has 300 transaction_amounts I need to sum up all of these transactions to find the tota transactions amount for the life of the job. And I need to do this for every job_id (5259 id's)

mweber87_0-1667840767502.png

 

@mweber87 
This is exactly what this code should give you. Are you sure this the grand total amount or this is just the total amount of the 300 transactions of Job_ID 16246? Would you please paste a screenshot of the code. Otherwise please try

Total Job Tranactions =
CALCULATE (
    SUM ( 'Table'[Transaction_Amount] ),
    ALLEXCEPT ( 'Table', 'Table'[Job_ID] )
)

 

@tamerj1 using the ALLEXCEPT could I add another column with a running total instead of sum total?

@mweber87 

Would you please clarify?

@tamerj1 I am now trying to make a column with a running total for each job_id. I wrote this but now my computer is stuck on trying to work on it. So maybe there is a more efficient way to get the result?

 

Rolling Total by Job Id=
VAR CurrentDate = d[transaction_date]

VAR CurrentJobID = d[Job_Id]
VAR Filtered = FILTER(d, d[Transaction_Amount] <= CurrentDate &&

d[Job_Id] = CurrentJobId)

 

Return

 

Calculate(SUM(d[transaction_amount]), Filtered)

@mweber87 
Please try

Rolling Total by Job Id =
VAR CurrentDate = d[transaction_date]
VAR CurrentJobTable =
    CALCULATETABLE ( d, ALLEXCEPT ( d, d[Job_Id] ) )
VAR Filtered =
    FILTER ( d, d[Transaction_date] <= CurrentDate )
RETURN
    SUMX ( Filtered, d[transaction_amount] )

@tamerj1 I just realized that did not do a running total based on the job_id, it just did a running total on the whole list.

@mweber87 
Oh!!! You are right. There is a mistake in the code. Please try

Rolling Total by Job Id =
VAR CurrentDate = d[transaction_date]
VAR CurrentJobTable =
    CALCULATETABLE ( d, ALLEXCEPT ( d, d[Job_Id] ) )
VAR Filtered =
    FILTER ( CurrentJobTable, d[Transaction_date] <= CurrentDate )
RETURN
    SUMX ( Filtered, d[transaction_amount] )

ahhh yes that worked! Thank you so much!!!

@mweber87 
Are you creating a measure of a calculated column? can you share some screenshot?

@tamerj1 

mweber87_0-1667931206740.png

A calculated column. At first I thought it was right because all of the job_id's in the beginning are the same number but if I filter to just one specific job_id it does not start a the first transaction_amount but more the amount where the running total is for example in this snip job_id 17150 starts on june 20 and the ifrst transaction_amount is $500.93 but it is showing as 792,369.88

mweber87_1-1667931434968.png

 

@tamerj1 the last code you sent worked! Thank you!!!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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