custom per-category formulae over summarised rows

Question

I'm trying to figure out some pivot table magic here, where I can have the top-level row-field be a custom formula for each category. So, for example, with data like this:

Edit: Providing in CSV format:

Period,Geo,Data Item,Value
Jan-21,Total,Cat 1,1.12
Jan-21,Total,Cat 2,1.62
Jan-21,DE,Cat 1,1.26
Jan-21,FR,Cat 1,1.45
Jan-21,CZ,Cat 1,1.4
Feb-21,Total,Cat 1,1.14
Feb-21,Total,Cat 2,1.7
Feb-21,DE,Cat 1,1.43
Feb-21,FR,Cat 1,1.47
Feb-21,CZ,Cat 1,1.5
Feb-21,CZ,Cat 3,1.2
~                     

I set up a pivot table like this:

I don't actually care about the inclusion of a row for each geo point, what I want is that for each category at each month it averages the geo values for that month/category, and then normalises them against an index of 100 for the first time the value occurs.

So, to be precise, where the pivot table currently shows 5.23 for Cat1/Jan-21, I want it to calculate off of a starting point of AVERAGE(1.4, 1.26, 1.45, 1.12) = 1.3075, and then turn that into 100 (1.3075/1.3075*100), such that Cat1/Feb-21 is AVERAGE(1.5, 1.43, 1.47, 1.14)/1.3075*100 = 105.92.

Then, similarly, for Cat 2 it should be based off a value of 1.7 instead of 1.3075; and for Cat 3, it should start with Feb-21 at 100 (since that's the first period where data appears).

I can get to the baseline number by going to Pivot Table Analysis -> Field Settings, and changing it to "Average". But, then I need to normalise the data. I tried adding a calculated field by going to Pivot Table Analysis -> Fields, Items, and Sets, and creating a new field called 'Cat1Norm' and giving it a formula of '=Value/1.3075*100'.

There are two problems I have from there:

First, while the calculation works correctly for each geo row, it no longer reports them as an average on the month summary rows, and I can't figure out how to change that from 'sum' to 'average'. Second, it adds a new sub-column for 'Cat1Norm' to every column — but I actually need that to be different for every column (in fact, I don't actually need it to be a sub-column, because I don't need the original, average data to appear at all).

Is there some sensible way to do this? Barring an answer here, my next step is probably to manually create another table that summarises the pivot table data, but ... it feels like there should be a way to do this.

As a final note, in case it wasn't clear from my textual descriptions above, my desired final output is something like

Answer 1

Summarize Values by Average:

Show values as % Of Period "21-Jan":

I suppose that will get you close, but perhaps won't work where the first period for a Category is not Jan of this year. Another option is to use % Of Period (previous), but that doesn't hold the index at the starting point.

Yet another option is to use PowerPivot and create your measure with DAX.

It might be simplest to alter your data as follows. Add three columns to your table:

=AVERAGEIFS([Value],[Period],[@Period],[Data Item],[@[Data Item]])
=XLOOKUP(MINIFS([Period],[Data Item],[@[Data Item]])&[@[Data Item]],[Period]&[Data Item],[PeriodAvg])
=[@PeriodAvg]*100/[@Baseline]

Then create the pivot table: