6 Replies Latest reply on May 17, 2018 6:33 AM by Jim Dehner

    Sum/count of aggregate measure?

    Andreas Nilsen

      Hi,

       

      This type of question has probably been asked before, but I can't find it anywhere.

       

      I have a column with multiple values for each project on different dates. The projects belong to regions. I want to take the average of each project, for example 94,5% for Project1 and check whether it's below 100% or not. Then I want to count the number of projects that didn't reach 100% on a regional level. So the sum (or count) of the failed Region A projects would be 1 and Region B would be 0 (since Project2 reached 100%). But Tableau doesn't let me do this because of the usual 'Cannot mix aggregate and non-aggregate...'-problem (as AVG is an aggregation), and I tried a number of other approaches too without success.

       

      Another issue is that the results change when I collapse the data into regions; the count or sum of the failed projects changes to '1' with the pass/didn't pass-calculation instead of summarizing all the number of projects that didn't reach 100%. But I would like to keep the result of the calculation on the regional perspective (e.g. 15 out of 20 projects in Region E failed etc).

       

      Does anybody know how to solve these issues?

       

      PeriodRegionProjectValue
      2018-01-01AProject199%
      2018-04-01AProject190%
      2018-03-01BProject2100%
      2018-07-01BProject2100%

       

      Here's the workbook - I saved a new version of it since the other one is quite big. I want to show the total number of failed projects in the circles, and then I plan to connect the results with actions to the table in the second sheet.