7 Replies Latest reply on May 7, 2015 1:06 PM by Sarma MVJ

# Aggregates based on Partitions

The sample denormalized data is follows

 Teacher  ID Program Id Participant Count 1 1 23 2 1 23 3 1 23 4 2 32 5 2 32

For example, there are 23 participants in program 1, which is taught by 3 teachers.

I am trying to get the following aggregated result in tableau.

 Teacher Count 5 Program Count 2 Participant Count 55

Any help is much appreciated.

• ###### 1. Re: Aggregates based on Partitions
 Teacher Count 5 Program Count 2 Participant Count 5

Teacher Count = COUNTD(Teacher ID)

Program Count= COUNTD(Program ID)

Participant Count= sum(COUNTD(Participant Count))

• ###### 2. Re: Aggregates based on Partitions

I doubt if the student count will work. As I said that's just sample data. Distinct cannot be applied and count cannot be used here. Any other suggestions?

Had it been SQL I can do SUM() based on partition of School ID. with tableau its a challenge for me.

• ###### 3. Re: Aggregates based on Partitions

If all the values are identical you can do SUM(AVG(Participant Count))  (Or SUM(MIN(...  or SUM(MAX(... )

I think some of this might be dependent on how you have the sheet layed out too.

• ###### 4. Re: Aggregates based on Partitions

Here is my solution

https://public.tableau.com/views/CountParticipants/Sheet3?:embed=y&:showTabs=y&:display_count=yes

using avg() for each partition then using sum() for grand total.

• ###### 5. Re: Aggregates based on Partitions

Thanks Joe and Alexander. I think I got the idea the using the double aggregates based on your solutions. That might solve my problem for now. I was thinking if there could someway to get the final total in one shot without the school details. May be that's how the data works.

Thanks for the help again.

• ###### 7. Re: Aggregates based on Partitions

Thanks a lot Alexander. That works perfectly.