7 Replies Latest reply on Mar 20, 2018 12:43 PM by Deepak Rai

    LOD - eliminating dupes with two tables that have different LOD

    Char Miller

      Hello -


      I am hoping someone  can help me with duplicate values. What I'm trying to do:


      (generalized synopsis): A customer buys a product for a total sale. The payments and discounts are recorded as separate transactions, each with its own code, ID and amount allocated to the sale. One table aggregates to sales total, discount total and payment total. The second table details each transaction by type. For example, if a customer bought Product A for $100, received a discount of type A for $25 and paid $75, Table One would aggregate this to one row with columns for total sale, total discount, total payment (100, 25 and 75, respectively). Table Two would list each transaction in separate rows, so there would be two rows: one with total sale, discount code, discount amount, and allocated amount of discount. The second would be total sale (repeated), payment code, payment amount and allocated amount of payment.


      I want the level of detail of the payment and discount codes without the repeating sales total. I can only get the codes from the table with the more granular level of detail.


      What I'm doing so far:


      1. Joining the two tables with different levels of detail ( have also tried data blending on the same tables) and linking/joining on Customer ID

      2. Trying a LOD calculation to keep the total only for the sale


      So far, no luck.  Here's an example (my workbook is attached with the same data):


      For customer ID 290, total sale should be 64+34+85 (for products A, B and C). For customer ID 500, total sale should be the same. I need to know what the total sale was and what the amounts of each discount or payment code were.


      Any suggestions? Workbook attached with same sample data.


      Thanks much!