5 Replies Latest reply on Nov 26, 2018 10:46 AM by Hari Ankem

# Totaling dollar amount per ticket when there is a one-to-many relationship with another related field

Hi all,

I have a set of data that includes a ticket number, a dollar amount affected per ticket and root causes per ticket. Some tickets have multiple root causes while others have just one root cause. Each ticket has a dollar amount associated with the ticket number. I would like to see a grand total amount that doesn't count the dollar amount for each ticket three times when there are three root causes. For example, TICKET-920 has \$8,000,000 affected. However, when I graph this the total shows as \$24,000,000. I know that it would be difficult and mostly inaccurate to assign the total amount of revenue affected to just one root cause to get the desired affect achieved, but I was thinking it would be best if each dollar amount per ticket could be divided by the number of root causes per ticket to help me get an accurate total per ticket and grand total for all tickets. Please see workbook attached. I'm not sure the best way to go from here. Any assistance you can provide is greatly appreciated.

Thanks!

• ###### 1. Re: Totaling dollar amount per ticket when there is a one-to-many relationship with another related field

Create a calculated field as shown below and use it.

Workbook is attached. Hope this helps.

• ###### 2. Re: Totaling dollar amount per ticket when there is a one-to-many relationship with another related field

Hi and thanks for your assistance. However, my management will not understand how there are 4 8 million dollar subtotals that equal a grand total of 9 million. I am looking to break down each Root Cause category subtotal by it's portion of the 8 million dollars for that particular ticket. How can this be done?

• ###### 3. Re: Totaling dollar amount per ticket when there is a one-to-many relationship with another related field

Does this look right now?

• ###### 4. Re: Totaling dollar amount per ticket when there is a one-to-many relationship with another related field

That does it! Thanks so much

• ###### 5. Re: Totaling dollar amount per ticket when there is a one-to-many relationship with another related field

You are welcome.