Hi, I have tried hard to answer my own question, but I have not been successful. I have a large table from which I have created a Pivot Table in Excel (office365). I have set up several filters to narrow what I want to see. The "columns" field in the "Pivot Table Fields" area has one "Region" field which in the data table covers many regions. Once the filters are applied only a few become visible in the pivot table (for example, only Regions 1, 5, 7, 10, 43 are shown). The "rows" field in the "Pivot Table Fields" area has one "Product" field which contains many products in the data table. Once the filters are applied, only a few become visible in the pivot table (for example, Product A, B and C). The "values" field in the "Pivot Table Fields" area has one "Max of Price" field. The resulting pivot table has three Product rows and five Region columns with max product prices for each column and row.
I have added a calculated item as a new row that computes the price difference between two products for each region.
I want to add a calculated field that computes a geomean of all regional product prices for a given product (in essence, compute a geomean for each row). I am not able to add this geomean column that operates across all visible columns for each row. If the filters are changed, the number of regions and products displayed could expand or shrink. The calculated field should operate correctly regardless of the size of the displayed table.
I'd appreciate any help you can provide.
Thank you.
Bookmarks