Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
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
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
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
Hi Swati,
Plz apply "Show Partial sums" on both "Category" and "Type".
You will get it.
Let me know.
Sincere Regards,
Happy Thoughts
Hi Swati,
Also, in expression, you have written "Score".
Use sum(Score).
Plz see attached file.
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