13 Replies Latest reply: Feb 5, 2016 2:44 AM by jagan mohan rao appala

# Pivot Table - How to use total partial sum value to create another expression

Hi guys,

I have created a pivot table, 1st expression called No. of checks(expression count(checks). That is fine. then i enable to Total Partial Sum. it gaves me a total of no.checks.

So now, my client want to know % checks - the measurement given by client is  - each number of check(1) / use total no. of checks(12)

1/12 = 0.083 x 100 = 8.3 %.

I couldn't get the value of  % checks.

Rgds,

Jim

• ###### Re: Pivot Table - How to use total partial sum value to create another expression

Hi,

Sum(No.of Check) /Sum(Total No.of Check)  , For that in number format fixed to 2 decimal and Show in Percent%,

If(Store='A',Sum({<Store={'A'}>}Check)/Sum(Total {<Store={'A'}>}Check),

If(Store='B',Sum({<Store={'B'}>}Check)/Sum(Total {<Store={'B'}>}Check)))

HTH,

PFA,

Hirish

• ###### Re: Pivot Table - How to use total partial sum value to create another expression

Thanks Hirish! Ur solution work well for my full total.  But when i look at my sub  total , it didnt give me the correct % checks.... suppose Store A - total no. of checks is 3 , so 1/3 = 0.3333, x 100, will be 33.33 %.

any idea why it is not affecting the sub totals???

Rgds,

Jim

• ###### Re: Pivot Table - How to use total partial sum value to create another expression

Hi,

Try like this

Count(Check)/Count(TOTAL  <Cover> Check)

• ###### Re: Pivot Table - How to use total partial sum value to create another expression

how r u bro!

the <Cover> not working.!

• ###### Re: Pivot Table - How to use total partial sum value to create another expression

u r referring to my "Cover" column right?

• ###### Re: Pivot Table - How to use total partial sum value to create another expression

Count(Check)/Count(TOTAL  <Store> Check)

• ###### Re: Pivot Table - How to use total partial sum value to create another expression

Hi,

It is Cover column.  Replce Cover with the actual dimension if it is different.  Is there any other Dimensions?  If yes include them also in the TOTAL like below

TOTAL <Dim1, Dim2, Dim3>

Regards,

jagan.

• ###### Re: Pivot Table - How to use total partial sum value to create another expression

Check above changed post of mine

• ###### Re: Pivot Table - How to use total partial sum value to create another expression

try like this with use of Total?

Check % = sum([No. Of Checks]) / sum( Total [No. Of Checks])

Note: In Format, change the format to Show in Percent(%) of Check%

• ###### Re: Pivot Table - How to use total partial sum value to create another expression

May be try

=num(Count(checks)/Count(TOTAL checks),'#,##0.0%')

• ###### Re: Pivot Table - How to use total partial sum value to create another expression

HI,

Try

Count(Check)/Count(TOTAL Check)

Hope this helps you.

Regards,

jagan.

• ###### Re: Pivot Table - How to use total partial sum value to create another expression

Count will not work

• ###### Re: Pivot Table - How to use total partial sum value to create another expression

Num(count(checks)/count(TOTAL checks),'#,#0.00%')