cancel
Showing results for
Did you mean:
Highlighted
Member

## Use Data in 2 different tables to form a Chart

Hi everyone,

Here is the link to my sample data. The 2 sheets are my 2 tables linked by Code and Serial columns.

There are 4 catergories of Description in the 'Data' table: LP PT ; HP PT ; PT VA ; HP CT.

The codes from 'Data'[Code] might or might not appear in the 'OPR'[Serial].

Question 1: Can you please show me how to create a measure that can calculate the Average TAT of the Codes from the 'Data' [Code]  using the 'OPR'[TAT]? The AVG TATs should be grouped into the 4 Description categories mentioned above.

Ex: (The numbers for this example are made up and they are different from the sample data I provided)

AVG TAT for LP PT = 45.5

AVG TAT for HP PT = 65.4

AVG TAT for PT VA = 23.5

AVG TAT for HP CT = 87.1

Question 2: Please advise how I can create a calculated column to calculate the Net Quantity of all the codes from the 'Data'[Code] using the 'OPR'[Quantity] and again group them into 4 Description Groups.

Ex: (The numbers for this example are made up and they are different from the sample data I provided)

Net Quantity for LP PT = 456

Net Quantity  for HP PT = 1254

Net Quantity for PT VA = 890

Net Quantity for HP CT = 1569

Question 3: Can you show me how to create a measure to calculate Average Cost for all the codes from the 'Data'[Code] using the 'OPR'[Cost] column and again group them into 4 Description catergories/groups (not code by code)?

Ex: (The numbers for this example are made up and they are different from the sample data I provided)

AVG Cost for LP PT = 456.7

AVG Cost for HP PT = 1231.3

AVG Cost for PT VA = 980.5

AVG Cost for HP CT = 1873.4

Question 4: How to create a measure or a calculated column to count the number of Codes in each Description group that APPEAR in the 'OPR' table and show what those codes are and which Stores they belong to?

Ex: (The numbers for this example are made up and they are different from the sample data I provided)

Number of Appear.Codes in LP PT = 10

List of Codes in LP PT = 301, 204, 894, 346, etc...

Number of Stores = 5

List of Stores = A, B, C, D, E

Question 5: Is there a way to Count the number of codes in each Description Group that DO NOT appear in the 'OPR' table and show what those codes and their corresponding groups?

Ex: (The numbers for this example are made up and they are different from the sample data I provided)

Number of NonAppear.Codes in LP PT = 4

List of Codes in LP PT = 331, 434, 634, 986

Number of Stores = 2

List of Stores = F, T