Forum Discussion
values duplicated in a join
I'd like to create a KPI chart by joining two tables together (1-* relationship) through an unique identifier that is created based on concatenation of 3 column values. however, when i try to pull both the revenue (from table 1) and targets (from table 2) into the same visual, the targets are being duplicated across all the categories. How can i resolve this?
Table 1:
| Category | Revenue | Period |
| Shopping | 100 | 2022Q4 |
| Travel | 200 | 2022Q4 |
| Dining | 300 | 2022Q4 |
Table 2:
| Category | Target | Period |
| Shopping | 2000 | 2022Q4 |
| Travel | 3000 | 2022Q4 |
| Dining | 4000 | 2022Q4 |
| Travel | 1000 | 2022Q3 |
| Dining | 1000 | 2022Q3 |
Expected Outcome:
| Category | Revenue | Target | Period |
| Shopping | 100 | 2000 | 2022Q4 |
| Travel | 200 | 3000 | 2022Q4 |
| Dining | 300 | 4000 | 2022Q4 |
What my report is showing now:
| Category | Revenue | Target | Period |
| Shopping | 100 | 11000 | 2022Q4 |
| Travel | 200 | 11000 | 2022Q4 |
| Dining | 300 | 11000 | 2022Q4 |
Error: it is summing up all the targets across all categories and period. how can i prevent this?
2 Replies
Hello elliee ,
Try making a seperate table that has the categories only, make a relationship between it and each of the two tables throught the category field.
Then use this field in the new table to show to show the data in the visuals.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Hi Idrissshatila , I'm using more than just category in the relationship.
For example:
Category Sub Category Customer Revenue Target Period Shopping A Alice 100 2000 2022Q4 Travel B Ben 200 3000 2022Q4 Dining A Charlotte 300 4000 2022Q4 I'm creating the relationship based on a concatenated field that joins Period, Category, Sub Category and Customer together. I will need the flexibility to report the performance at the different levels.
The option to add as new query is not available when i select all the dimensions used in the relationship. how can i achieve this report please?
