Pivottable formula that uses Values from Pivot Table

[Hjallis]

Hi,

In the attached xlsx file with a pivot table in which I would like to calculate the average percentage by dividing the "Sum of %" by the "Count of X".
Is it possible to insert this calculation in the Pivot in a simple way somehow?

Thank you in advance,
Sincerely
Hjallis

[Norie]

You can add a calculated field for that.

Select the pivot table, goto PivotTable Analyze>Fields, Items & Sets>Calculated Field...

Enter an appropriate name for the field, enter this formula,

='%' /X * 10

and click Add then OK.

The new field should now appear in the pivot table and you can change it's format by right clicking it and selecting Number Format...

[Hjallis]

Thanks Norie for you replay,
Unfortunately I was not completely clear in my example before, the value of X is not always 10 but could be any number big or small, negative or positive. So I can't use the multiplier "10".

What I think I need to use is the Field "Count of X" somehow... The Value of X is not important, only how many time it is true.

please see my new attachment.

Thanks again!
Hjallis

[JeteMc]

Hello Hjallis and Welcome to Excel Forum.
I would imagine that someone may be able to do what you want using explicit measures applied to the data placed in the data model.
As four days have passed I thought that I might offer a formula based alternative, until such time as the above is offered.
The Modell column is populated using: =IFERROR(INDEX(Table1[Modell],MATCH(0,INDEX(COUNTIF(F$4:F4,Table1[Modell]),,),)),"")
The Sum of % column is populated using: =IF(F5="","",SUMIFS(Table1[%],Table1[Modell],F5))
The Count of X column is populated using: =IF(F5="","",COUNTIFS(Table1[Modell],F5,Table1[X],"<>"))
The %/X column is populated using: =IF(F5="","",G5/H5)
Let us know if you have any questions.

[JeteMc]

OK, I fudged a bit on this by adding another column (Count X) to the data.
Count X is populated using: =IF([@X]<>"",1,0)
The data is then placed in the data model and three measures are added:
Sum of %:=SUM(Table1[%])
Count of X:=SUM(Table1[Count X])
%/X:=[Sum of %]/[Count of X]
Note that the Sum of % and %/X are formatted in the data model so that they will not need to be formatted again when the pivot table is produced.
The three measures are then used in the pivot table modeled on the Table sheet.
Let us know if you have any questions.