5 Replies Latest reply on Sep 11, 2018 2:02 PM by Deepak Rai

    Dividing aggregates from two separate secondary data sources

    Connor Martin

      Hi all, I'm trying to calculate a percentage of completed audits. I have a primary data source (A) that's broken out by month, year, branch. One secondary data source (B) connects which contains a row for the month if an audit was completed, so the numerator. And one more secondary data source (C) that has a row for each branch and each month it was possible for them to complete an audit, so the denominator.

       

      (C) does not contain 18 of the branches, so for these I'd like the denominator to be just how many months we are into the year, so 8. There are branches with fewer than 8 depending on location.

      Tableau is resisting using my calculation for these branches even though my numerator and denominator are clearly showing at the branch level correctly in my crosstab. Where might I be going wrong?

       

      Below are my current calculations:

      numerator --

      sum(B.[self audits])

       

      denominator --

      if isnull(sum(C.[Number of Records]))

      then 8

      else SUM(C..[Number of Records])  END