Forum Discussion
Linking Tables
Hi,
I have two tables that i am trying to link using common identifier and bring the corresponding values from one table to another. When i import the table and link with the common identifier (Product Hierarchy) and bring the corresponding Gross Sales it is summing up the total rather than bringing individual values. For Ex: Sump Pump 1 Gross Sales value should be $1,567,970 not total Sump Pump gross sales of "$299,369,096 "What may cause this issue?
| Product Hierarchy | Gross Sales | Franchise Sales |
| Sump Pump 1 | $299,369,096 | 0 |
| Sump Pump 2 | $299,369,096 | 200 |
| Sump Pump 3 | $299,369,096 | 2520 |
| Sump Pump 4 | $299,369,096 | 1460 |
| Sump Pump 5 | $299,369,096 | 4160 |
| Sump Pump 6 | $299,369,096 | -9740 |
| Sump Pump 7 | $299,369,096 | 0 |
| Sump Pump 8 | $299,369,096 | 0 |
| Sump Pump 9 | $299,369,096 | 85510 |
| Sump Pump 10 | $299,369,096 | 92420 |
| Sump Pump 11 | $299,369,096 | 0 |
| Sump Pump 12 | $299,369,096 | 0 |
| Sump Pump 13 | $299,369,096 | 95650 |
| Sump Pump 14 | $299,369,096 | 87380 |
| Sump Pump 15 | $299,369,096 | 0 |
| Sump Pump 16 | $299,369,096 | 0 |
| Sump Pump 17 | $299,369,096 | 0 |
| Sump Pump 18 | $299,369,096 | 0 |
| Sump Pump 19 | $299,369,096 | 0 |
| Sump Pump 20 | $299,369,096 | 35250 |
| Sump Pump 21 | $299,369,096 | 90010 |
Hi,
Please find attached the PBI file.
Hope this helps.
6 Replies
Hi,
Some problem with the relationship. To receive further help, share data in a format that can be pasted in an MS Excel file and show the expected result.
Thank you Ashish.
There are two tables i am trying to link to. One is called "Smmary", second one is called Gross Sales. Output table will have Gross Sles right next to Franchise OIS where Calendar Year is 5/31/2023. I linked Product Hierarchy and Calendar year in both tables and setup many to one relationship. It doesnt allow many to many relationship.
After linking tables it shows wrong numbers for Gross Sales.
Hope this clarifies.
Output Table
Calendar Year Product Hierarchy Invoice Sales Gross Sales 5/31/2023 Sump Pump 1 -2 $8,948 5/31/2023 Sump Pump 2 56,324 $5,082 5/31/2023 Sump Pump 3 9,731 $10,908 5/31/2023 Sump Pump 4 6,230 $85,625 5/31/2023 Sump Pump 5 0 $158,391 5/31/2023 Sump Pump 6 0 $113,247 5/31/2023 Sump Pump 7 24 $7,466 5/31/2023 Sump Pump 8 0 $99,211 5/31/2023 Sump Pump 9 0 $89,401 5/31/2023 Sump Pump 10 672 $1,214,519 5/31/2023 Sump Pump 11 532 $93,926 5/31/2023 Sump Pump 12 0 $31,913 5/31/2023 Sump Pump 13 0 $180,980 5/31/2023 Sump Pump 14 157,571 $69,384 5/31/2023 Sump Pump 15 4,833 $161 5/31/2023 Sump Pump 16 0 $85,898 5/31/2023 Sump Pump 17 2,848 $279,286 5/31/2023 Sump Pump 18 25,425 $12,516 5/31/2023 Sump Pump 19 4,606 ($839) Summary
Calendar Year P&L Product Hierarchy Values 5/31/2022 Units Sump Pump 19 1,161 5/31/2022 Units Sump Pump 20 478,981 5/31/2023 Invoice Sales Sump Pump 1 $8,948 5/31/2023 Invoice Sales Sump Pump 2 $5,082 5/31/2023 Invoice Sales Sump Pump 3 $10,908 5/31/2023 Invoice Sales Sump Pump 4 $85,625 5/31/2023 Invoice Sales Sump Pump 5 $158,391 5/31/2023 Invoice Sales Sump Pump 6 $113,247 5/31/2023 Invoice Sales Sump Pump 7 $7,466 5/31/2023 Invoice Sales Sump Pump 8 $99,211 5/31/2023 Invoice Sales Sump Pump 9 $89,401 5/31/2023 Invoice Sales Sump Pump 10 $1,214,519 5/31/2023 Invoice Sales Sump Pump 11 $93,926 5/31/2023 Invoice Sales Sump Pump 12 $31,913 5/31/2023 Invoice Sales Sump Pump 13 $180,980 5/31/2023 Invoice Sales Sump Pump 14 $69,384 5/31/2023 Invoice Sales Sump Pump 15 $161 5/31/2023 Invoice Sales Sump Pump 16 $85,898 5/31/2023 Invoice Sales Sump Pump 17 $279,286 5/31/2023 Invoice Sales Sump Pump 18 $12,516 5/31/2023 Invoice Sales Sump Pump 19 ($839) 5/31/2023 Invoice Sales Sump Pump 20 $476,981 Gross Sales
Calendar Year Product Hierarchy Gross Sales Segment Values 5/31/2023 Sump Pump 1 Gross Sales High End $18,948 5/31/2023 Sump Pump 2 Gross Sales High End $15,082 5/31/2023 Sump Pump 3 Gross Sales High End $20,908 5/31/2023 Sump Pump 4 Gross Sales High End $95,625 5/31/2023 Sump Pump 5 Gross Sales High End $168,391 5/31/2023 Sump Pump 6 Gross Sales High End $123,247 5/31/2023 Sump Pump 7 Gross Sales High End $17,466 5/31/2023 Sump Pump 8 Gross Sales High End $109,211 5/31/2023 Sump Pump 9 Gross Sales High End $99,401 5/31/2023 Sump Pump 10 Gross Sales Low End $1,224,519 5/31/2023 Sump Pump 11 Gross Sales Low End $103,926 5/31/2023 Sump Pump 12 Gross Sales Low End $41,913 5/31/2023 Sump Pump 13 Gross Sales Low End $190,980 5/31/2023 Sump Pump 14 Gross Sales Low End $79,384 5/31/2023 Sump Pump 15 Gross Sales Low End $10,161 5/31/2023 Sump Pump 16 Gross Sales Low End $95,898 5/31/2023 Sump Pump 17 Gross Sales Low End $289,286 5/31/2023 Sump Pump 18 Gross Sales Low End $22,516 5/31/2023 Sump Pump 19 Gross Sales Low End $9,161 5/31/2023 Sump Pump 20 Gross Sales Low End $486,981 Hi,
Please find attached the PBI file.
Hope this helps.
