Sum and Count at the bottom of the same table.

[rahulsud007]

Dear all

I have requirement in which I need count of items and sum of items in the bottom row as mentioned in the image in a straight or pivot table.

But in Single object not in multiple objects.

Please suggest me.

[sunny_talwar]

Are you trying to get this?

For Sum I used the Total on Dimension Limits tab:

For Count I used the Total on the Expression tab and gave it the label on Presentation tab:

[rahulsud007]

Hi Sunny n Kuczynska, I need count of only the values present greater than 0. !

!

[sunny_talwar]

This?

Changed the expressions a little bit:

Expression1: =If(Sum([0-1M]) <> 0, Sum([0-1M])) (from Sum([0-1M]))

and so on....

Also changed the Null Symbol from - to 0.000 and made it right justified on presentation tab:

I hope this will help.

Best,

Sunny

[rahulsud007]

That was very helpful, Sunny.

Is it possible to have a percentage column below count where the formula is total of 0-1M / total inventory for 0-1 M and total of 0-2M/total Inventory for 0-2 n so on..?

[sunny_talwar]

I don't think we can have 3 Total columns, I can recommend another solution where you can have three totals in the one row instead of 3. Do you think that would work???

[rahulsud007]

Please suggest, Lets me see it works or not

[rahulsud007]

Sunny !, the third total should take the formula as I have mentioned

[sunny_talwar]

This is the alternative method I was talking about:

Expression1:

=If(Dimensionality() = 0, Num(Sum([0-1M]), '#,##0.000') & Chr(10) &

  Num(Count([0-1M]), '#,##0.000') & Chr(10) &

  Num(Sum([0-1M])/Sum(TotalInventory), '#,##0.00%'),

Num(Sum([0-1M]), '#,##0.000'))

Output:

Only drawback here is that since we need three totals at the bottom, the complete chart needs to have cell height of 3:

Also attaching the qvw for reference.

HTH

Best,

Sunny

[rahulsud007]

Hi Sunny,

Thanks a lot for your help.

Your mentioned solution worked well in QV but when we extract to Excel we need to Manually wrap the column.

Is there any Similar solution where we just need to pull the data to excel and it will be in similar format.

[sunny_talwar]

Hey Rahul -

Unfortunately, these are the only two methods I knew to show more than two totals in a table. If there is a third method, I would be more than happy to know about it.

I hope someone can help you out with this.

Best,

Sunny