4 Replies Latest reply on Dec 5, 2018 5:00 PM by Janet Jenkins

    Filter a calculation when dimensions are in different tables

    Janet Jenkins

      I am very new to Tableau, so if anyone is able to answer my question please may I have the reply in simple terms if possible. Please excuse me if my terminology is not 100% correct.

       

      I need to create a calculation that gives me a percentage of applications containing errors in a month – this part I can do. I have created a calculated field that divides the number of errors by the number of applications and displays the result as a percentage.

       

      My next challenge is to be able to filter the calculation by teams submitting the applications to give the team’s percentage of applications with errors (and switch back and forth between teams). A team can be made up of 2 or more groups so the filter needs to be capable of multiple selections.

      As soon as I apply a team filter the error rate shows as 100% because the applications are in one sheet (table) in an Excel workbook and the errors are in another. The teams are a common dimension in both tables. The filter appears to be only being applied to the errors dimension and not the applications.

       

      • As an example a team has 17 applications in October, 9 of which have errors. When I apply the team filter in Tableau I am getting 9 applications and 9 errors, so 100% error rate – clearly wrong. It works fine if I select all teams, so applying the filter is causing my pain.

       

      The tables and dimensions are set up as:

      Table1 - Data_Deals_Master with dimensions:

      • Lender Name
      • Business Unit
      • Deal ID
      • Month

      Table 2 – Data_Errors_Master with dimensions:

      • Lender Name
      • Business Unit
      • Deal ID
      • Month
      • Error ID

       

      Note – there are 2 tables as a Deal ID is unique in the Deals Master, but a Deal ID may have multiple errors in the Error Master. I can use CountD in my calculation of % Deals with Errors between the 2 tables.

       

      I hope this makes sense – I am sorry it is not possible for me to share the workbook as it contains sensitive information.

      The answer is probably something like creating a parameter or calculated field, which I have fumbled my way through before without really understanding what I was doing, but I would need to be stepped through the process in this case.

       

      Many thanks in advance for any assistance - I hope someone can help :-)