Calculate Error Rate from two tables

[Gusdate2]

Thank You for looking at this post.

I have been given 2 tables, I do not control or have input into what I am given.

The first is a list of actions performed by team members. It contains the Team Member Name, Task Performed, Date Performed and the Count of the Task perfomed. There is no Primary key, so unfortunately there could be valid rows that share the same values

The second is a list of errors. It contains Team Member Name, Task Performed, Date Error Found, Error Type 1, Error Type 2. It also does not have a Primary Key, so again rows could share the same values.

So whatI am being asked to do is provide a simple dashboard that shows the Team Member, the tasks they performed and their error rate. They want to be able to filter by month if they choose. They also want to be able to filter by error type. I have been spinning my wheels on this for 2 days. I tried a couple different approaches but each failed validation. I am doing something wrong I just can't figure out what.

So ultimate goal is a Matrix Visual with Team Member and Task as Row, Month as Column and Error Rate for Value.

I put together a quick example data set. Example Data 

One other thing to note, the actual data set is currently 275K records with roughly 500 team members ad 60 tasks and 13 months of data.

I hope my description is clear. Thank you in advance for any assistance offered. I did not include any attemps that I have tried because I am betting there is a way better solution than the approaches I was trying. Thank you again for looking!!

[tamerj1]

Hi @Gusdate2 

You have to start from here

[Gusdate2]

@tamerj1 This is a great start, Thank You. Truthfully I had tried the reference tables already in my work version, where I am falling down is on the error rate. The measure that ties the sum of errors to the sum of Step Counts. 

I didn't include the reference tables in my example becauase I didn't want to limit the relationships to those reference tables. Maybe the solution is a merged column foreign key? I have no idea.

I should have clarified what error rate entails.

The error rate is the count of errors over the sum of step counts.

Example...if the question is...what was Aaron's Error Rate for Ship in Feb?

The answer would be (1 type1 error + 1 type2 error)/9 Step Counts...2/9...22.22%

But if they then say What was Aaron's Error Rate for Ship and Error Type1 in Feb?

This answer would be (1 type1 error)/9 Step Counts...1/9...11.11%

To add to that...if the question was what was Aaron's over all error rate for Feb?

This would be (0 +1 +1 + 1 + 1 + 0)/(16 + 9 + 23)...4/48...8.33%

If the question is Aaron's overall error rate for Feb type 1 errors?

This would be (0 + 1 + 1)/(16 + 9 + 23)...2/48...4.17%

This is where I am falling down...

Any additional insight you have would be greatly welcome.

[tamerj1 · Accepted solution]

@Gusdate2 

It is better to unpivot the type1 and type2 columns and have them as Attribute - Value

[Gusdate2]

@tamerj1 Thank you again for the response. I can unpivot the errorlog table and create an attribute and value. But again, I need help answering the questions raised above. I have not been able to create a measure to do that. If you, or someone else is able to assist me creating the measures necessary to answer the questions in my previous reply, that would be fantstic. Thank You 🙂

[tamerj1]

@Gusdate2 

If the type columns in the error table were unpivoted then why not just

DIVIDE ( SUM ( Data[Step Count] ), SUM ( Errot[Value] ) )?

Am I missing something?

[Gusdate2]

@tamerj1 I don't think you are missing anything...I unpivoted the error log, but the relationships broke. So in my head I was thinkng "AARRRGGGHHH"

I went to play more and I realized that when I unpivoted, it changed the Month to text. So I changed it back to Date and the relationships came back.

So I created Total Errors and Total Step Count measures and then Error Rate as Total Errors/Total Step Count.

Initial look at  that approach was good.

I will try the method you listed above too.

WOOHOO...I am in a good mood now.

Yesterday I spent my time trying to use the related and relatedtable function and brute force creating new summarized tables. I didn't think to unpivot. Thank You sir!!! I am super pleased.