Use Data in 2 different tables to form a Chart

[trdoan]

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

Please advise! Thank you sooooo muchhh!

[JarroVGIT]

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 🙂