Joining multiple tables, and then converting rows to columns

[Anonymous]

Hello --

I have a project to create a production variance dashboard. I'm in the initial stages of trying to gather all of my data.

One of the key aspects to this dashboard is it needs to display a straight table that shows the work orders completed on a day, the qty completed, and break out the material, labor, and overhead costs to then calculate some variances.

They wish to see this table like so:

Part No	Work Order	Qty Completed	Std. Material Cost	Std. Labor Cost	Std. Overhead Cost
123456	XYZ	10	.05	.28	.32

The trouble I am having is that my data exists in 3 tables as follows:

Transaction Table:

Part Number	Work Order	Qty Completed
123456	XYZ	10

Cost Table:

Part Number	Cost_ID	Std. Cost
123456	1	.05
123456	2	.28
123456	3	.32

Cost_ID Table:

Cost_ID	Cost_Description
1	Std. Material Cost
2	Std. Labor Cost
3	Std. Overhead Cost

How do I join these various tables together, while converting the "rows" of the cost descriptions into columns in order to display the values the way they wish? Further more, I'll then need to utilize the various costs to do further calculations.

I appreciate your help!

[#2 · Accepted solution]

i see.

in fact, even having data model i proposed, u may still use straight table. Simple create expressions for each KPI using set analysis.

Of course you may also create your model like Marco suggested. Of course the question is, what else (except this chart) you plan to do in your application

regards

Darek

[Anonymous]

Yes! This is exactly what I was looking for. Thank you so much.

Now I can display the data in the straight table, but still have access to the raw information for other sheets/visualizations within the application.

Thank you again!