How to calculate an average of two fields in power pivot table?

[astrbac]

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:

[TABLE="width: 700"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Cost[/TD]
[TD]Revenue[/TD]
[TD]Return on investment[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]30[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]400[/TD]
[TD]2000[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]23[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]50[/TD]
[TD]1000[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]


Formula

Return on inv. = Revenue / Cost

The problem
If I draw a pivot table out of this dataset, It looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Sum of Return on inv.[/TD]
[TD]Avg of Return on inv.[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]25[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


The correct result - Average return on inv. for the whole day 1. is 5.84.

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

 

[steve the fish]

Im no expert on these formulas but it will be something like:

ROItotal:=calculate(sum(Table1[Revenue])/sum(Table1[Cost]))

which should then appear as a field in the pivot table.

 

[astrbac]

Hi Steve,

thanks for taking the time to reply, much appreciated! Did you mean that I should use this formula that you wrote as a new "Calculated field" in my pivot table? If so, I'm afraid I will not be able to use that... My pivot tables are drawn as Power pivot (from Excels data model) and the option to create new calculated fields is greyed out and unclickable.

I have to do this in the "Transform" window where I write M language formulas. Thanks, I will investigate along these lines that you wrote

Cheers!

 

[steve the fish]

Go to the table in power pivot. In one of the cells below the grey line place the formula. From the table create a pivot table. In that pivot you will have a new field.

 

[peter789]

If you are using Power Pivot you need to create three new measures. One each for the SUM of cost and revenue and a third for SumRevenue/SumCost (although using DIVIDE would be better practice).
Peter

PS I think this is the same method Steve the Fish proposed but he suggested creating the measures in the calculation area of the Data Model. I usually write measures directly in the field list.