Calculate values from different rows in the same column into a different column

[Williamkb]

Entry Date	ID	Value	Total
1/1/2022	AA	1	8
1/2/2022	AB	2	9
1/3/2022	AC	3	0
1/4/2022	AD	4	4
1/5/2022	BA	5	8
1/6/2022	BC	6	6
1/7/2022	BD	7	7
1/8/2022	BB	8	8
1/9/2022	CC	4	4
1/10/2022	CA	3	0
1/11/2022	CB	6	6
1/12/2022	CD	7	7
1/13/2022	BB	9	0
1/14/2022	CC	2	0
1/15/2022	DD	1	1
1/16/2022	FC	6	6
1/17/2022	FD	7	7
1/18/2022	AA	8	0

 I need to add 2 or more rows together from the Value column and put the results into the Total column.

For Example in the above table 'AA' and 'BD' were added together and the results put in 'AA' Total row. Notice that the Value in the 'BD' row was added to nothing and placed in the 'BD' Total column. Since ID can repeat I need to make sure to only get the Value from the latest date.

Also 'AB' and 'CD' was added together and the results placed in the 'AB' Total row, etc ...

Extra info: The ID's that add together always are added together and I always need to get the latest data date per ID. This data goes back 6+ years and has hundreds of thousands of rows.

Any help would be appreciated. I am relatively new to PBI so please try to keep it simple and explain in detail.

Thank you.

[ppm1]

This is doable but what is the logic to decide which IDs to combine? Why do AA and BD go together? Is there another column not provided? Also, this could be done in Power Query or with DAX. Power Query would be recommended.

Pat

[Williamkb]

Hi Pat,

Thank you for responding.

Unfortunetly the logic to which ID's are combined is human choice. This data is pulled from a government public site and represents different sections/business sectors/economic key points of the US economy. My customer has selected which ID's pretains to their business and therefore which ID's should be combined (added, subtracted or averaged).

The good news is the ID's are consistent, so AA and BD will always be added together. Once the logic of the code is hammered out it will be rare for changes to occur.

 Thank you again for your help.

William

[Williamkb]

I tried something like this but it didn't actually add the 2 values together. I'm hoping there is somone out there who likes a challenge and can help me out with this.

IF(FIRSTNONBLANK('External Data'[Dataset ID], 1)="AA",
CALCULATE(
CALCULATE(
SUM('TABLE'[Value]),
FILTER(
'TABLE',
CONTAINS('TABLE', 'TABLE'[ID], "AA")
),
FILTER(
'TABLE', 'TABLE'[Entry Date]=MAX('TABLE'[Entry Date])
)
) +
CALCULATE(
SUM('TABLE'[Value]),
FILTER(
'TABLE',
CONTAINS('TABLE', 'TABLE'[ID], "BD")
),
FILTER(
'TABLE', 'TABLE'[Entry Date]=MAX('TABLE'[Entry Date])
)
),
FILTER(
'TABLE', 'TABLE'[Entry Date]=MAX('TABLE'[Entry Date])
)
),
CALCULATE(
SUM('TABLE'[Value]),
FILTER(
'TABLE', 'TABLE'[Entry Date]=MAX('TABLE'[Entry Date])
)
)
)