Getting % of sub groups in pivot tables using Aggr()

[lucasdavis500]

Hello all, I have a pivot table where I'm trying to get % of the # worked, but I have two dimensions in the table so I'm not sure how I should go about this

Example Table: Currently

                                                     # Worked       %
Name           Type

John Doe     Type1                             50              -

                     Type2                            100            -

                     Type3                            10              -

                     Type4                             40             -

Lucy Lou      Type1                            100              -

                     Type2                            200            -

                     Type3                            20              -

                     Type4                             60             -

Both Name and Type are Dimensions. Name is a calculated dimension, where I use a variable that has stored

IF(MATCH('Names I Want', OriginalNameField))

Type just a variable I created.

I want my pivot table to resemble this:

                                                     # Worked       %
Name           Type

John Doe     Type1                             50              25%

                     Type2                            100            50%

                     Type3                            10              5%

                     Type4                             40             20%

Lucy Lou      Type1                            100            26.31%

                     Type2                            200            52.63%

                     Type3                            20              5.26%

                     Type4                             60             15.78%

where the '%' is the percentage of the # worked for that specific type, and that analyst, out of the total worked by that analyst

Also, is there a way to sort within these groups too? For example John Doe's data, sorted, by # Worked, would be 100, 50, 40, 10.

I tried doing this with the AGGR() function but could not, for the life of me, get the results I wanted.

[sunny_talwar · Accepted solution]

May be this:

Sum([# Worked])/Sum(TOTAL <OriginalNameField> [# Worked])

[lucasdavis500]

Hi Sunny - thanks for the reply

Somethng like this?



=COUNT({<[someDummy] = {'0'}, anotherDummy = {'1'}, DateType = {'Created'}, lastDummy = {'1'}>} Pkey)
  /
COUNT(TOTAL <ResolvedBy> {<[someDummy] = {'0'}, anotherDummy = {'1'}, DateType = {'Created'}, lastDummy = {'1'}>} Pkey)

[sunny_talwar]

Yes