Forum Discussion
combining calculated columns from two separate tables both linked to a common table
Hello,
Situation: I am trying to categorize company spend (IT, services, supplies, etc) and there are two data sources. Both of these sources have an Accounting code for each transaction. To get the spend category, I have a master table that shows the corresponding spend category to each code. In BI, I have both sources linked to the same master table. I have created calculated columns in each table to find the spend subcategory and parent category. However, I am unable to combine these two sources so that I can create visualizations. Does anyone have any suggestions/guidance?
Example:
Source 1:
| Date | Vendor | Description | G/L Account | Subcategory (Calculated Column) | Category (Calculated Column) | Spend |
| 6/6/2019 | ABC Vendor | pens | 5400 | Office Supplies | Corporate | $5,500 |
| 6/7/2019 | XYZ Vendor | Power bi | 5475 | Software | IT | $50,000 |
| 6/8/2019 | FGH Vendor | data entry | 5070 | Purchased Services | Professional Services | $10,000 |
Source 2:
| Date | Vendor | Description | G/L Account | Subcategory (Calculated Column) | Category (Calculated Column) | Spend |
| 6/6/2019 | QRS Vendor | pencils | 5400 | Office Supplies | Corporate | $5,500 |
| 6/7/2019 | LMN Vendor | laptop | 5470 | Hardware | IT | $50,000 |
| 6/8/2019 | UVW Vendor | Temp Labor | 5070 | Purchased Services | Professional Services | $10,000 |
In the resulting table, I'd have 6 rows, and if I create a visualization, I'd see that $20,000 was spent on Purchased Services in June from two different companies.
Thank you!
Hi sac1995 ,
For your scenario, you could try to append the two query in query editor like below.
Then you could get the output.
Best Regards,
Cherry
1 Reply
Hi sac1995 ,
For your scenario, you could try to append the two query in query editor like below.
Then you could get the output.
Best Regards,
Cherry