Spotfire Sum over multiple levels

Question

I have an expense file that includes a separate record for each attendee but repeats the total expense amount.

Employee    Report ID   Transaction Date    Vendor           City/Location  Expense Type    Approved Amount Attendee Name
John Smith  A           4/20/2016           UNITED AIRLINES  NYC            Airfare         317.1           Jane smith
John Smith  A           4/20/2016           UNITED AIRLINES  NYC            Airfare         317.1           jack smith
John Smith  A           4/20/2016           UNITED AIRLINES  NYC            Airfare         317.1           tom white
John Smith  A           4/20/2016           Bar and Grill    NYC            Dinner          94.74           kelly thompson
John Smith  A           4/20/2016           Bar and Grill    NYC            Dinner          94.74           joan ellen
John Smith  A           4/20/2016           Bar and Grill    NYC            Dinner          94.74           albert coals

I need to sum the total of the expenses by city without duplicating the expense amount. I have tried the following custom expression while doing a cross table on city:

Sum(max([Approved Amount]) over (Intersect([Transaction Date],[Vendor])))

But this has resulted in

City  Amount
NYC   1235.52

I am looking for a result like

City  Amount
NYC   411.84

Any suggestions would be appreciated.

Answer 1

@cookiemnstr247 - Please test the below solution and let me know if it works.

Step 1: Inserted calculated column 'Rank'

Rank([Approved Amount],"desc",[Employee],[Transaction Date],[City/Location],[Vendor],"ties.method=first")

Step 2: Inserted another calculated column 'sumvalue'

If([Rank]=1,[Approved Amount],0)

Step 3: Created a cross table as shown in the screen shot below

I have tested this solution with different scenarios and it seems to be stable.

Note: Employee, Transaction date, city/Location and Vendor are only used for grouping in this case. If you would like add more columns to grouping, please add those to rank column