topic Re: Sum parts of a column based on a different column in DAX Commands and Tips

Sum parts of a column based on a different column

mweber87 — Mon, 07 Nov 2022 16:24:42 GMT

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.

Re: Sum parts of a column based on a different column

tamerj1 — Mon, 07 Nov 2022 16:46:59 GMT

Hi mweber87
Please try

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

Re: Sum parts of a column based on a different column

mweber87 — Mon, 07 Nov 2022 16:59:41 GMT

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

Re: Sum parts of a column based on a different column

tamerj1 — Mon, 07 Nov 2022 17:04:57 GMT

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.

Re: Sum parts of a column based on a different column

mweber87 — Mon, 07 Nov 2022 17:08:53 GMT

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)

Re: Sum parts of a column based on a different column

tamerj1 — Mon, 07 Nov 2022 17:16:19 GMT

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] ) )

Re: Sum parts of a column based on a different column

mweber87 — Tue, 08 Nov 2022 14:36:57 GMT

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

Re: Sum parts of a column based on a different column

mweber87 — Tue, 08 Nov 2022 15:24:22 GMT

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

Re: Sum parts of a column based on a different column

tamerj1 — Tue, 08 Nov 2022 15:50:52 GMT

mweber87

Would you please clarify?

Re: Sum parts of a column based on a different column

mweber87 — Tue, 08 Nov 2022 16:15:33 GMT

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)

Re: Sum parts of a column based on a different column

tamerj1 — Tue, 08 Nov 2022 17:02:54 GMT

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] )

Re: Sum parts of a column based on a different column

mweber87 — Tue, 08 Nov 2022 18:05:46 GMT

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.

Re: Sum parts of a column based on a different column

tamerj1 — Tue, 08 Nov 2022 18:08:18 GMT

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

Re: Sum parts of a column based on a different column

mweber87 — Tue, 08 Nov 2022 18:17:22 GMT

tamerj1

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

Re: Sum parts of a column based on a different column

tamerj1 — Tue, 08 Nov 2022 19:00:42 GMT

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] )

Re: Sum parts of a column based on a different column

mweber87 — Wed, 09 Nov 2022 15:26:23 GMT

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