Hi all,
I have such a basic question that it pains me to even ask, but I just can't crack it. I use pivot tables extensively and have the following problem:
Source data:
<tbody>
</tbody>
Formula
The problem
If I draw a pivot table out of this dataset, It looks like this:
<tbody>
</tbody>
The correct result - Average return on inv. for the whole day 1. is 5.84.
How can I get the correct result in my pivot tables? It may be important to mention that I use "Get and transform" and power pivot so I cannot use "Calculated fields" in my pivot tables. All of the available fields are already loaded and transformed from .csv files.
Many thanks!
Alex
I have such a basic question that it pains me to even ask, but I just can't crack it. I use pivot tables extensively and have the following problem:
Source data:
Day | Cost | Revenue | Return on investment |
1 | 30 | 0 | 0 |
1 | 10 | 0 | 0 |
1 | 400 | 2000 | 5 |
1 | 23 | 0 | 0 |
1 | 50 | 1000 | 20 |
<tbody>
</tbody>
Formula
Code:
Return on inv. = Revenue / Cost
The problem
If I draw a pivot table out of this dataset, It looks like this:
Day | Sum of Return on inv. | Avg of Return on inv. |
1 | 25 | 5 |
<tbody>
</tbody>
The correct result - Average return on inv. for the whole day 1. is 5.84.
Code:
Total revenue/Total cost
How can I get the correct result in my pivot tables? It may be important to mention that I use "Get and transform" and power pivot so I cannot use "Calculated fields" in my pivot tables. All of the available fields are already loaded and transformed from .csv files.
Many thanks!
Alex