Use Data in 2 different tables to form a Chart

Hi trdoan 

That are a lot of questions you packed in the post. Thanks for providing excel data, that helps us help you 🙂

Question 1: you load the data in PowerBI, create a one-to-many relationship between Data[Code] and OPR[Serial]. Create a measure like this:

AVG TAT = AVERAGE(OPR[TAT]) 

Then create a table visual with first column Data[Description] and [AVG TAT] measure. Result is this:

Note that the blank value is there because there are entries in OPR[Serial] that are not in the Data table. 

Question 2: I don't understand why you want a calculated column (I don't think a calculated column is what you think it is to be honest). Your output is similar as question 1 so why not also use a measure? Anyway, the calculated column is like this:

NetQuantity = SUMX(RELATEDTABLE(OPR), [Quantity])

Adding it to the same Table visual as question1 (setting the summarization to SUM) results in this:

Question 3: Similar as question1, so let's create the measure:

AVG Cost = AVERAGE(OPR[Cost]) 

Adding it to the Table visual results in:

Question 4: Calculated columns and Measures return a scalar value (e.g. 1 value). A calculated column returns 1 value for every row, a measure returns 1 value for every evaluation. You are asking for sliced data, but don't mention the visual you want to use for this. You can't return lists of data, please explain how you want to visualize this.

Question 5: Again, see question 4. You can only return scalar values. Number of stores is not correct here (how can you count the number of stores if you are looking at non-existing rows?) so this measure will return the count of Descriptions that are not present in the OPR dataset:

CodesNotInOPR = 
VAR _curDesc = SELECTEDVALUE(Data[Description])
VAR _tmpTable = ADDCOLUMNS(Data, "occurenceInOPR", 
                        VAR _curCode = [Code]
                        RETURN
                        COUNTROWS(FILTER(OPR, OPR[Serial] = _curCode))) 
RETURN
COUNTROWS(FILTER(_tmpTable, [occurenceInOPR] = 0))

Adding this to the table visual:

See attached my pbix (with your data). Ingore other tables, they are there for helping others. Your table visual is on Page 2.

Let me know if this helps you out, and please accept it as a solution if it does 🙂