I have a big spreadsheet in a pivot table.
One of my Fields is "Retiring Within 5 Years." I have this as my Report Filter, and I'm selecting only those records which say "Yes."
Then in my row labels, I've selected just the top ten job titles in several groups.
I'd like to divide the "Count" value by the total (unfiltered) universe of employees in each group.
Is this possible?
Of course, but only in Power Pivot using the calculation field
Probably, using something like countifs, but to be of more assistance, we would need to see a sample of what you have - and what you want
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
FDibbins -
I've attached a demo of what I'm talking about. You'll see in the pivot I have "Groups" and "Titles," and the individuals in their are either "Retiring" or not (Col. E).
So in the pivot table, I've filtered to just those people who are retiring. You'll see in Column I what I'd like to get at: The Group row, I'd like to divide the number retiring by the unfiltered number in that Group, and then have it as a percentage. In the Title rows, I'd like to divide the number retiring by the unfiltered number sharing that title, as a percentage.
Is there any way to do this in pivot? (I cannot install PowerPivot.)
This proposed solution sets up a psuedo calculated field.
First step is to continue with the calculations being made in the ranges below the pivot table by adding columns to count the instances of 'Yes' and 'No' using the formulas (respectively):Formula:
Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
The formula that populates I4:I23 is:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Working through constructing this in my larger database right now - any chance you could help me parse the meaning of I4:I23 formula? I'm getting lost in it.
First "IF" of formula will display a blank if the corresponding cell in column G is either blank or "Grand Total".
Second "IF" of formula will display 100% if the report filter is "All".
Third "IF" will display if the report filter is "Yes",
"Value if True" part of third "IF" -- if the name in column G is a group name then it will use the values in rows rows 32:37. If the name in column G isn't a group name then it will trigger the IFERROR and look for the name in rows 40:52. Once the name is found it will divide the corresponding value in column I by the corresponding value in column H.
"Value if False" part of third "IF" -- will display if the report filter is "No" again first looking through the group names. Only difference is that it will divide the corresponding value in column J by the corresponding value in column H.
A good way to see this in action would be to set the report filter to "Yes" and select cell I8 (Teller's percentage). Run the Evaluate Formula feature from the Formulas tab.
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks