Itemized entries vs Total Amount

[gats1527]

We have a situtation where we are pulling data from a source that lumps the total together but when the data is entered on an expense report, they will itemize it out (breakit down between 2 entries) but from a reporting standpoint we want to see the total amount from the 1st course displayed once but then the separate amounts listed in their own columns.  Attached is an example and below is the clear example we are trying to solve.

On the attached screenshot, the column highlighted "Posted Amount (credit card currency)" is the total amount of 173.89 for the line item which we need to have displayed once but then in the "Expense Amount (reimbursement currency) the total amount is split up between 2 lines items, 35.50 and 138.9 so when that is happening, the 173.89 is being displayed twice thus increasing our Posted Amount and not correct.  We basically need to have the 173.89 displayed once and the 35.50 and 138.90 displayed as well

Any thoughts?

tom

[Andrei I]

We basically need to have the 173.89 displayed once and the 35.50 and 138.90 displayed as well

So you have now:

Personal	Posted Amount(Credit)	Expense Amount
N	173.89	35.50
Y	173.89	138.90

Do you want to get this?

Personal	Posted Amount(Credit)	Expense Amount
N	173.89	35.50
Y		138.90

[gats1527]

That is correct.  The Personal Y/N information pertains to the 35.50 and 138.90 lines but yes the 173.89 needs to display once

[Andrei I]

Then how do you decide which line to show the subtotal 173.89?
With Personal=Y or  Personal=N ?

[adam_mc]

Use conditional formatting to only display the value of the "Total" column when the Y/N column = 'N'.
However, if you are totaling this column further down the page, you may need to do something else for that total.

Hope this helps.
Adam.

[gats1527]

we actually dont need the total amount of the 173.89 to appear on a particular line but just once

[gats1527]

Use conditional formatting to only display the value of the "Total" column when the Y/N column = 'N'.
However, if you are totaling this column further down the page, you may need to do something else for that total.

Hope this helps.
Adam.

How would i set up this conditional format?

[Andrei I]

Say, you want to show the Subtotal on the First line only.

Personal	Posted Amount(Credit)	Expense Amount
N	173.89	35.50
Y		138.90

Then you can create a new hidden Data Item [RC]=running-count(1 for [ROW ID])
where [ROW ID] is a combination of attributes which make your row unique.
Also you would define a Subtotal = total(Posted Amount(Credit) for [ROW ID])
And you would show a Subtotal Column as Posted Amount(Credit)
So you apply Conditional Formatting on the this Column to make its Value Visible  only when [RC]=1
See attached image for an example

[gats1527]

Say, you want to show the Subtotal on the First line only.

Personal

Posted Amount(Credit)

Expense Personal Posted Amount(Credit) Expense Amount N 173.89 35.50 Y 138.90 Then you can create a new hidden Data Item [RC]=running-count(1 for [ROW ID]) where [ROW ID] is a combination of attributes which make your row unique. Also you would define a Subtotal = total(Posted Amount(Credit) for [ROW ID]) And you would show a Subtotal Column as Posted Amount(Credit) So you apply Conditional Formatting on the this Column to make its Value Visible  only when [RC]=1 See attached image for an example This has worked perfectly but the issue i am running into now is that when i subtotal that column, it is still including those hidden values thus skewing the total number.  So if i have have the subtotal column = 5,000 but then the other columns break down the expenses between 3 lines, the 5,000 will appear once but then when i run the subtotal on that Subtotal column, it will multiply the 5,000 by the 3 total columns for the unique row

[gats1527]

This has worked perfectly but the issue i am running into now is that when i subtotal that column, it is still including those hidden values thus skewing the total number.  So if i have have the subtotal column = 5,000 but then the other columns break down the expenses between 3 lines, the 5,000 will appear once but then when i run the subtotal on that Subtotal column, it will multiply the 5,000 by the 3 total columns for the unique row.  Basically we would want the subtotal for the Posted Amount field to only total those rows when the RC field = 1

[Andrei I]

Can you use my example and add required subtotals/totals?
Then I will figure out how to build this layout

Personal	Posted Amount(Credit)	Expense Amount
N	173.89	35.50
Y		138.90