Manipulate %

[jaiiracha]

Hi - I have a sample dataset below where I have and ID field, Date field and Amount % field.

The problem I am trying to solve is that currently for each unique ID, the sum of the Amount % = 100% but I would like to reduce the number of decimal places to 2 and manipulate the last (MAX date) Amount % value such that the total for each unique ID still = 100%. Any help will be greatly appreciated!

ID	Date	Amount %
ID_101	7/1/2023	0.00000000000000
ID_101	8/1/2023	8.33332530600961
ID_101	9/1/2023	4.16666265300480
ID_101	10/1/2023	4.16666265300480
ID_101	11/1/2023	4.16666265300480
ID_101	12/1/2023	4.16667641413119
ID_101	1/1/2024	4.16666265300480
ID_101	2/1/2024	4.16666265300480
ID_101	3/1/2024	4.16667641413119
ID_101	4/1/2024	4.16666265300480
ID_101	5/1/2024	4.16666265300480
ID_101	6/1/2024	4.16667641413119
ID_101	7/1/2024	4.16666265300480
ID_101	8/1/2024	4.16666265300480
ID_101	9/1/2024	4.16667641413119
ID_101	10/1/2024	4.16666265300480
ID_101	11/1/2024	4.16666265300480
ID_101	12/1/2024	4.16667641413119
ID_101	1/1/2025	4.16666265300480
ID_101	2/1/2025	4.16666265300480
ID_101	3/1/2025	4.16667641413119
ID_101	4/1/2025	4.16666265300480
ID_101	5/1/2025	4.16666265300480
ID_101	6/1/2025	4.16667641413119
ID_102	10/1/2023	4.16666014492867
ID_102	11/1/2023	4.16666014492867
ID_102	12/1/2023	4.16667971014266
ID_102	1/1/2024	4.16666014492867
ID_102	2/1/2024	4.16666014492867
ID_102	3/1/2024	4.16667971014266
ID_102	4/1/2024	4.16666014492867
ID_102	5/1/2024	4.16666014492867
ID_102	6/1/2024	4.16667971014266
ID_102	7/1/2024	4.16666014492867
ID_102	8/1/2024	4.16666014492867
ID_102	9/1/2024	4.16667971014266
ID_102	10/1/2024	4.16666014492867
ID_102	11/1/2024	4.16666014492867
ID_102	12/1/2024	4.16667971014266
ID_102	1/1/2025	4.16666014492867
ID_102	2/1/2025	4.16666014492867
ID_102	3/1/2025	4.16667971014266
ID_102	4/1/2025	4.16666014492867
ID_102	5/1/2025	4.16666014492867
ID_102	6/1/2025	4.16667971014266
ID_102	7/1/2025	4.16666014492867
ID_102	8/1/2025	4.16666014492867
ID_102	9/1/2025	4.16667971014266

Thank you in advance!!!

[Raj]

@jaiiracha IF you round it till 2 decimal places this is not going to work and we should not round the final this without complete information
as it is possible that you miss some transaction with some small value and this can cause error

without rounding this is perfect -100

after forced 2 places this become -100.08 and 100.07 and we should not put imaginary condition in this case.

[jaiiracha]

You're exactly right, and to avoid that, what I am trying to achieve is that I would like to round everything to 2 decimal places but then manipulate the last (MAX date) Amount % value such that the total for each unique ID still = 100%.

So for ID_101, the last Amount % (Date = 6/1/2025) = 4.10 after rounding all preceding values to 2 decimal places (see table below).

ID	Date	Amount %
ID_101	7/1/2023	0.00
ID_101	8/1/2023	8.33
ID_101	9/1/2023	4.17
ID_101	10/1/2023	4.17
ID_101	11/1/2023	4.17
ID_101	12/1/2023	4.17
ID_101	1/1/2024	4.17
ID_101	2/1/2024	4.17
ID_101	3/1/2024	4.17
ID_101	4/1/2024	4.17
ID_101	5/1/2024	4.17
ID_101	6/1/2024	4.17
ID_101	7/1/2024	4.17
ID_101	8/1/2024	4.17
ID_101	9/1/2024	4.17
ID_101	10/1/2024	4.17
ID_101	11/1/2024	4.17
ID_101	12/1/2024	4.17
ID_101	1/1/2025	4.17
ID_101	2/1/2025	4.17
ID_101	3/1/2025	4.17
ID_101	4/1/2025	4.17
ID_101	5/1/2025	4.17
ID_101	6/1/2025	4.10

Hope that makes sense. Appreciate the help!

[binuacs]

@jaiiracha