Splitting a value to two decimal places using AGGR function with no remainder

[jonbshaw]

Hi,

Please could someone help me find the solution to the below problem.

=Sum([Gross Amt])/aggr(NODISTINCT count([OrderRef]),[Invoice Number])

Gives me 3,185.54 split exactly 6 ways to equal 530.9233333 on each row.  I need the values to be to two decimal places, but the value has to be exactly 3,185.54, there can't be any remainder, so all of the rows can't equal 530.92 or 530.93

What I have:
Invoice Number	Gross Amt	Order Ref	=Sum([Gross Amt])/aggr(NODISTINCT count([OrderRef]),[Invoice Number])
901031	3185.54	28935801	530.9233333
901031	3185.54	28937601	530.9233333
901031	3185.54	28937701	530.9233333
901031	3185.54	28937901	530.9233333
901031	3185.54	28939301	530.9233333
901031	3185.54	28946801	530.9233333

What I need:
Invoice Number	Gross Amt	Order Ref	=Sum([Gross Amt])/aggr(NODISTINCT count([OrderRef]),[Invoice Number])
901031	3185.54	28935801	530.93
901031	3185.54	28937601	530.93
901031	3185.54	28937701	530.92
901031	3185.54	28937901	530.92
901031	3185.54	28939301	530.92
901031	3185.54	28946801	530.92

Any help would be very much appreciated.

Regards,

Jon

[Vegar]

I don't see how you can do what you want to do, but even if all your rows displays 530.92 your totalling should be correct as long you do not change the totalling from the  default (expression total) to Sum of Rows. 

[jonbshaw]

Thanks very much for your response.

I need to be able to upload this output into a separate system, so it has to be exact, like in my 'What I need' table.

The total in the Gross Amt column is an invoice total so it can't be changed, the numbers I need in the new column have to equal the Gross Amt when added together, and they have to be to two decimal places.