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

# Sum/count of aggregate measure?

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.

• ###### 1. Re: Sum/count of aggregate measure?

Good morning

the first issue results from measures/dimensions in your formula that are aggregated mixed with some that are not - if you will post your workbook in a TWBX format with  the data we can pinpoint the problem - bear in mind that sum() is not the only aggregate function - think min(), max(),, Attr(), for example

The second issue depends on the structure of your data and the dimensions within the data - please post your workbook

Jim

1 of 1 people found this helpful
• ###### 2. Re: Sum/count of aggregate measure?

Hi again, I saw another another reply that you made Jim, and it might actually work for this problem. It was the {INCLUDE ...} option. If I use the calculation below, it seems to return the correct number of projects below 100%:

{INCLUDE [Projekt]:

IF AVG([Value]) < 1 THEN COUNTD([Projekt]) END }

• ###### 3. Re: Sum/count of aggregate measure?

Is this what you are looking for?

-AV

1 of 1 people found this helpful
• ###### 4. Re: Sum/count of aggregate measure?

Hi Andreas,

Is this the result you want? -

I used some fix calculations to work out the value per project, date and region. Attached is a workbook for more details.

Thanks,

Mavis

1 of 1 people found this helpful
• ###### 5. Re: Sum/count of aggregate measure?

Actually this one might be better because Region B had two project project 4s. I don't know if you wanted to count this once or twice, but in the below I've only counted them once:

Attached the workbook here too.

Thanks,

Mavis

1 of 1 people found this helpful
• ###### 6. Re: Sum/count of aggregate measure?

Hi

see the attached - I don't come back to the numbers in your post - I guess that is because of the data you included

in chart form this is what I get

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful