Using a pivot table's calculated field to sum up conditional values
<p>I have the following data set:</p>

<pre><code>+--------+---------+----------+
|  Date  | Revenue | Expected |
+--------+---------+----------+
| Dez 5  |      51 |        1 |
| Dez 4  |      72 |        1 |
| Dez 3  |      72 |        1 |
| Dez 2  |      59 |        1 |
| Dez 1  |      81 |        1 |
| Nov 30 |      50 |        1 |
| Nov 29 |      53 |        1 |
| Nov 28 |      99 |        0 |
| Nov 27 |      85 |        0 |
| Nov 26 |      65 |        0 |
| Nov 25 |      94 |        0 |
+--------+---------+----------+
</code></pre>

<p>The column <code>Expected</code> determines whether or not the <code>Revenue</code> number has been confirmed. Now, I want to have a pivot table that sums up the revenues per month. The final result should be this:</p>

<pre><code>+-------+-------------------+-----+
| Month |                   |     |
+-------+-------------------+-----+
| Nov   | Revenues          | 446 |
|       | Expected Revenues | 103 |
| Dez   | Revenues          | 335 |
|       | Expected Revenues | 335 |
+-------+-------------------+-----+
</code></pre>

<p>I can't seem to get this working for the month November, because my calculated field looks like this:</p>

<pre><code>=IF(Expected*1&gt;0;Revenues*1;0)
</code></pre>

<p>I had to add <code>*1</code> because it wouldn't work for some numbers, so you can mentally ignore this for the moment.</p>

<p>In any case, the result of this is the following:</p>

<pre><code>+-------+-------------------+-----+
| Month |                   |     |
+-------+-------------------+-----+
| Nov   | Revenues          | 446 |
|       | Expected Revenues | 446 |
| Dez   | Revenues          | 335 |
|       | Expected Revenues | 335 |
+-------+-------------------+-----+
</code></pre>

<p>Can anyone point me in the right direction here? I understand that the reason for this is most likely that the Expected column gets summed up first and then the IF comparison comes about. Is there a way to circumvent this?</p>