
How to calculate subtotals in a pivot table for a particular column values?
Ravi Achaliya Jul 14, 2010 3:24 AM (in response to swatisinha17)Hi Swati,
In pivot table chart properties, go to presentationgo 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?
swatisinha17 Jul 14, 2010 3:39 AM (in response to Ravi Achaliya)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?
Ravi Achaliya Jul 14, 2010 3:48 AM (in response to swatisinha17)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?
swatisinha17 Jul 14, 2010 4:09 AM (in response to Ravi Achaliya)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

Scorecard_Apr_2010.qvw 143.5 K

How to calculate subtotals in a pivot table for a particular column values?
Ravi Achaliya Jul 14, 2010 4:48 AM (in response to swatisinha17)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

Scorecard_Apr_2010 Subtotals.qvw 136.0 K

How to calculate subtotals in a pivot table for a particular column values?
swatisinha17 Jul 15, 2010 1:10 AM (in response to Ravi Achaliya)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

Scorecard_Apr_2010.qvw 143.2 K

How to calculate subtotals in a pivot table for a particular column values?
Ravi Achaliya Jul 15, 2010 1:42 AM (in response to swatisinha17)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?
swatisinha17 Jul 15, 2010 2:06 AM (in response to Ravi Achaliya)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

Scorecard_Apr_2010.qvw 143.2 K

How to calculate subtotals in a pivot table for a particular column values?
Ravi Achaliya Jul 15, 2010 2:12 AM (in response to swatisinha17)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?
Ravi Achaliya Jul 15, 2010 2:02 AM (in response to swatisinha17)Hi Swati,
Also, in expression, you have written "Score".
Use sum(Score).
Plz see attached file.

Scorecard_Apr_20101507.qvw 143.2 K

How to calculate subtotals in a pivot table for a particular column values?
swatisinha17 Jul 15, 2010 2:20 AM (in response to Ravi Achaliya)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






