Forum Discussion
Linking Tables
Hi,
Please find attached the PBI file.
Hope this helps.
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.
Thank you Ashish. I link the tables similar to what you did however Gross Sales outputs shows as sum. It doesnt show corresponding values for each brand. Please see below
if it helps ic an share the .pbix file with you. Thanks
Study my solution carefully over and over again before you give up.