Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hello, you might have some answers to this. I have a table and I need a sum of the amount of my jobID if the jobID about is its latest date. Below is my table
ID | Name | Job ID | About | Date |
1 | A | 10001 | $ 600.00 | 1/1/2021 |
1 | A | 10001 | $ 1,000.00 | 1/1/2022 |
1 | A | 10002 | $ 40.00 | 1/1/2021 |
1 | A | 10002 | $100,000.00 | 1/1/2022 |
1 | A | 30001 | $ 803.00 | 1/1/2021 |
1 | A | 30001 | $ 611.00 | 1/1/2022 |
1 | A | 401 | $ 214.00 | 1/1/2021 |
1 | A | 401 | $ 91,111.00 | 1/1/2022 |
1 | A | 12 | $ 85.00 | 1/1/2021 |
1 | A | 12 | $ 23.00 | 1/1/2022 |
1 | A | 901 | $ 1.00 | 1/1/2021 |
1 | A | 901 | $ 700.00 | 1/1/2022 |
1 | A | 532 | $ 56.00 | 1/1/2021 |
1 | A | 532 | $100,022.00 | 1/1/2022 |
1 | A | 222 | $ 34.00 | 1/1/2019 |
1 | A | 222 | $ 23.00 | 1/1/2022 |
1 | A | 10001 | $ 1.00 | 1/1/2019 |
1 | A | 79 | $ 50,000.00 | 1/1/2020 |
so the one with the yellow mark should only be added. So the sum should be
$343,491.00 |
Hope you can help me. Thanks a lot
Solved! Go to Solution.
Hi, Thanks for the response, appreciate your help by the way can we try this table.
Name | JobID | Amount | Date |
A | 11 | $4 | 1/1/2021 0:00 |
A | 11 | $0 | 1/1/2022 0:00 |
A | 62 | $0 | 1/1/2021 0:00 |
A | 62 | $0 | 1/1/2022 0:00 |
A | 21 | $1 | 1/1/2021 0:00 |
A | 21 | $0 | 1/1/2022 0:00 |
A | 101 | $58,493 | 1/1/2021 0:00 |
A | 101 | $53,163 | 1/1/2022 0:00 |
A | 77 | $1,000 | 1/1/2021 0:00 |
A | 77 | $0 | 1/1/2022 0:00 |
A | 42 | $30 | 9/1/2020 0:00 |
A | 85 | $0 | 1/1/2022 0:00 |
A | 71 | $8,507 | 1/1/2021 0:00 |
A | 71 | $10,321 | 1/1/2022 0:00 |
A | 25 | $0 | 2/1/2021 0:00 |
A | 49 | $286 | 1/1/2021 0:00 |
A | 49 | $0 | 1/1/2022 0:00 |
A | 88 | $13,293 | 2/1/2021 0:00 |
A | 57 | $8,326 | 1/1/2021 0:00 |
A | 57 | $8,799 | 1/1/2022 0:00 |
so the total should be
$85,606 |
If you want to total to show correctly as well, try it with a SUMX,
Latest Amount =
SUMX (
VALUES ( 'Table'[Job ID] ),
VAR _MaxDate =
CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Job ID] ) )
RETURN
CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Date] = _MaxDate )
)
If you want to total to show correctly as well, try it with a SUMX,
Latest Amount =
SUMX (
VALUES ( 'Table'[Job ID] ),
VAR _MaxDate =
CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Job ID] ) )
RETURN
CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Date] = _MaxDate )
)
Commission | RenewalDate |
$238,966 | 4/1/2020 0:00 |
$51,718 | 5/1/2020 0:00 |
$87,436 | 6/1/2020 0:00 |
$204,107 | 7/1/2020 0:00 |
$244,060 | 8/1/2020 0:00 |
$753,967 | 9/1/2020 0:00 |
$47,273 | 10/1/2020 0:00 |
$207,433 | 11/1/2020 0:00 |
$23,588 | 12/1/2020 0:00 |
$280 | 12/31/2020 0:00 |
$4,785,567 | 1/1/2021 0:00 |
$246,811 | 3/1/2021 0:00 |
$224,783 | 4/1/2021 0:00 |
$54,839 | 5/1/2021 0:00 |
$213,057 | 6/1/2021 0:00 |
$64,151 | 7/1/2021 0:00 |
$231,259 | 8/1/2021 0:00 |
$650,859 | 9/1/2021 0:00 |
$45,247 | 10/1/2021 0:00 |
$161,187 | 11/1/2021 0:00 |
$19,009 | 12/1/2021 0:00 |
$0 | 12/31/2021 0:00 |
$5,328,343 | 1/1/2022 0:00 |
$121,892 | 3/1/2022 0:00 |
$180,999 | 4/1/2022 0:00 |
$1 | 5/1/2022 0:00 |
$0 | 6/1/2022 0:00 |
Hello, can you help me with this one? to capture the sum of the year 2022?
Thank you this one works fine!
@dreyes6 , Try a measure like
latest About =
VAR __id = MAX ('Table'[Job ID] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[Job ID] = __id )
return
CALCULATE ( Sum ('Table'[About] ), VALUES ('Table'[Job ID] ),'Table'[Job ID] = __id,'Table'[Date] = __date )
Thrid last row should not come
Hi, Thanks for the response, appreciate your help by the way can we try this table.
Name | JobID | Amount | Date |
A | 11 | $4 | 1/1/2021 0:00 |
A | 11 | $0 | 1/1/2022 0:00 |
A | 62 | $0 | 1/1/2021 0:00 |
A | 62 | $0 | 1/1/2022 0:00 |
A | 21 | $1 | 1/1/2021 0:00 |
A | 21 | $0 | 1/1/2022 0:00 |
A | 101 | $58,493 | 1/1/2021 0:00 |
A | 101 | $53,163 | 1/1/2022 0:00 |
A | 77 | $1,000 | 1/1/2021 0:00 |
A | 77 | $0 | 1/1/2022 0:00 |
A | 42 | $30 | 9/1/2020 0:00 |
A | 85 | $0 | 1/1/2022 0:00 |
A | 71 | $8,507 | 1/1/2021 0:00 |
A | 71 | $10,321 | 1/1/2022 0:00 |
A | 25 | $0 | 2/1/2021 0:00 |
A | 49 | $286 | 1/1/2021 0:00 |
A | 49 | $0 | 1/1/2022 0:00 |
A | 88 | $13,293 | 2/1/2021 0:00 |
A | 57 | $8,326 | 1/1/2021 0:00 |
A | 57 | $8,799 | 1/1/2022 0:00 |
so the total should be
$85,606 |
User | Count |
---|---|
84 | |
77 | |
76 | |
43 | |
36 |
User | Count |
---|---|
109 | |
56 | |
52 | |
45 | |
43 |