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

    Sara Nelms

      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!