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

# LOD - eliminating dupes with two tables that have different LOD

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!

• ###### 1. Re: LOD - eliminating dupes with two tables that have different LOD

{FIXED Customer ID, Product: AVG(Total Sale)}

• ###### 2. Re: LOD - eliminating dupes with two tables that have different LOD

I tried that as a calculated field. The totals are not right - should be \$183 per customer, or \$366 total. Not sure what I'm doing wrong - if you have a chance to take another look, I'd greatly appreciate it. I"ll attach the updated workbook.

• ###### 3. Re: LOD - eliminating dupes with two tables that have different LOD

Can you save your workbook in a version below 10.5 from FILE>Export as Version. I need to see what is happening

• ###### 4. Re: LOD - eliminating dupes with two tables that have different LOD

Yes - I uploaded a version in 10.2. Thanks so much -

• ###### 5. Re: LOD - eliminating dupes with two tables that have different LOD

There are 2 ways to get it. I have done it by Calculation 4 and Calculation 7. Just follow the Calculations , I did.

Thanks

Deepak

If it Helps, pl mark it Helpful and CORRECT.

1 of 1 people found this helpful
• ###### 6. Re: LOD - eliminating dupes with two tables that have different LOD

Thank you so much!

• ###### 7. Re: LOD - eliminating dupes with two tables that have different LOD