Pivot Table Column with Value and Calculated Field row per group?

1
votes

The more I have to work with Excel in helping our customers with advanced reporting, the more I'm amazed at it's capabilities. That being said, I can't seem to find exactly what this customer wants Excel to do.

Scenario: We have a datasource which refreshes a worksheet containing many rows and columns of a data export from the customer's database. That worksheet then drives the other tabs' charts, tables, etc.

They want to add a table (pivot) which will take their milestones, sum the (ontime) column, and then take the (rownumber) sum for that area and then divide it so they can see how many projects were on time. Simplified data tab looks like this:

RowNum     Area     OnTime     Milestone
------     ----     ------     ---------
1          North       1       M2
2          East        1       M4
3          East        1       M2
4          North       0       M1
5          East        0       M4

and here's the table they want it to produce.

enter image description here We can get it to do both using a Sum(value) and calculated field, however we can't get the Sum(value) field to go directly beneath the calculated percentage field. Any ideas?

1
excelpivot-tablecalculated-field

1 Answers

2
votes

If I understand correctly, it will be enough to move the Values drawer to your Rows:

enter image description here

If you need to change the order of the values (which one will be at the top etc.), you can do this moving the drawers within Values Window at bottom-right.