11 Replies Latest reply: Jul 15, 2010 2:20 AM by swatisinha17

# How to calculate subtotals in a pivot table for a particular column values?

Hi All,

Please refer to the attached xls file.

I am importing data from this xls file into a pivot table. I have two columns in the excel, that is, Score and Category. I need to calculate the total score for different categories.

For example:

Category Score

customers 10

20

5

SubTotal: 35

Internal Processes 20

10

30

SubTotal: 60

Pls. suggest how to represent data in this form in a Pivot table in QV?

Regards,

Swati Bansal

• ###### How to calculate subtotals in a pivot table for a particular column values?

Hi Swati,

In pivot table chart properties, go to presentation-go to "Dimensions and Expressions" (top left corner) select category and tick show partlal sums (below rectangle).

You will get such results.

Happy Thoughts

• ###### How to calculate subtotals in a pivot table for a particular column values?

Hi Ravi,

I tried this but nothing was calculated in total. Secondly, I have a query. How does QV know that it has to calculate sub totals for the Score column, as nowhere we have mentioned that totals are to be calculated for Score values.

Regards,

Swati

• ###### How to calculate subtotals in a pivot table for a particular column values?

Hi swati,

I hope your Category is in Dimensions.

Can you post Excel file for which you want this subtotal?

By ticking Show partial sums, we instruct QV to give subtotal for that particular dimension.

• ###### How to calculate subtotals in a pivot table for a particular column values?

Hi Ravi,

I am attaching the qvw file for your reference. The xls is the same I have attached in my previous posts.

Actuallly I wanted to calculate subtototals for the score column, but score was earlier an Expression. Even after converting it into Dimension, i am not able to calculate the total of score values for different categories.

You can try yourself from the attached qvw file.

Regards,

Swati

• ###### How to calculate subtotals in a pivot table for a particular column values?

Hi Swati,

Problem is identified.

Plz refer to the attachment.

First table "Scorecard" that you had created was a "straight table".

I converted it to "Pivot Table".

You can get such subtotals only in Pivot table not in Straight table.

Happy Thoughts

• ###### How to calculate subtotals in a pivot table for a particular column values?

Hi Ravi,

I have used pivot table only. Pls. see the attached file.

I have a category column which has two values:Customers and Internal Processes.

Another column is Score.

Now I need to calculate the total score for Customers and Internal Processes.

Pls. suggest how to achieve this. I cudn't do it with the approach you suggested.

Thanks & Regards,

Swati Bansal

• ###### How to calculate subtotals in a pivot table for a particular column values?

Hi Swati,

Plz apply "Show Partial sums" on both "Category" and "Type".

You will get it.

Let me know.

Sincere Regards,

Happy Thoughts

• ###### How to calculate subtotals in a pivot table for a particular column values?

Even after applying partial sums on both Category and Type, I don't get the desired result. It doesn't give me the total of the "Score" column. You can see in the attached qvw file.

Regards,

Swati

• ###### How to calculate subtotals in a pivot table for a particular column values?

Hi Swati,

After posted two replies to your query.

Probably you have not seen it.

You have to use Sum(Score) instead of Score in expression.

It will work.

• ###### How to calculate subtotals in a pivot table for a particular column values?

Hi Swati,

Also, in expression, you have written "Score".

Use sum(Score).

Plz see attached file.

• ###### How to calculate subtotals in a pivot table for a particular column values?

Hi Ravi,

Thanks for your reply. It is working now, after using sum(Score) in the Expression and using Partial Sums in the "Presentation" tab for the "Type" column.

Thanks again.

Regards,

Swati