Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
@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] )
)
@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] )
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
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
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?
@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?
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
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
9 | |
6 |