Forum Discussion

Gusdate2's avatar
Gusdate2
Icon for Helper I rankHelper I
3 years ago
Solved

Calculate Error Rate from two tables

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 Tea...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Gusdate2 

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

Gusdate2's avatar
Gusdate2
Icon for Helper I rankHelper I
3 years ago

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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Gusdate2 

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

  • Gusdate2's avatar
    Gusdate2
    Icon for Helper I rankHelper I
    3 years ago

    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's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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's avatar
        Gusdate2
        Icon for Helper I rankHelper I
        3 years ago

        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.