HI Elly,
I've not used PivotItems (and confess that I couldn't get Rory's solution to work either - but that will be an error I'm making and possibly because I'm using the data model approach).
I adopted a different approach using PowerPivot - it seems more longwinded, but it worked for me:
1. When creating the pivot table ensure that the tick box for 'Add to Data Model' is ticked.
2. Create your pivottable with Date as Rows.
3. Create 4 'measures' by right clicking on the table name in Pivot Table Fields (top box) and selecting Add Measure
then in the Measures box create each measure: CountHits, CountNo, CountYes and PercentSuccess, which are the Measure Names and the formula's for each are as follows. Note that by selecting 'Category' you can preformat the values.
CountHits: =COUNTA(Table1[Target Hit?])
CountNo: =CALCULATE([CountHits],Table1[Target Hit?]="No")
CountYes: =CALCULATE([CountHits],Table1[Target Hit?]="Yes")
PercentSuccess: =[CountYes]/[CountHits]
I then added the three measures: CountNo, CountYes and PercentSuccess to the values area of the pivot table with the following result:
| Book1 |
|---|
|
|---|
| A | B | C | D |
|---|
| 3 | Row Labels | CountNo | CountYes | PercentSuccess |
|---|
| 4 | Jan | 1 | 1 | 50% |
|---|
| 5 | Feb | | 1 | 100% |
|---|
| 6 | Mar | 1 | 3 | 75% |
|---|
| 7 | Grand Total | 2 | 5 | 71% |
|---|
|
|---|
Apologies, this is more long winded than Rory's answer, but I think that PowerPivot (with Powerquery) is a very powerful solution - you can mix information even from different source tables.
HTH.
