Gfrktyl

I have an ODATA data source that, simplified, has the following columns:

Name

LifetimeBudget

FY2016Budget

FY2017Budget

...

FY2070Budget

I want to create a pivot table that will display the fields above in the following order:

Name, FYxxxxBudget, ..., FYxxxxBudget, SelectedYearsBudget,
LifetimeBudget

The FYxxxxBudgets are a range where the user can select which years they are interested in and the SelectedYearsBudget column would display a calculated total for only the included years (where LifetimeBudget is from the datasource, but contains the total budget over all years in the table - this can be recreated as a calculated field if it helps).

For example:

Name    FY2020Budget    FY2021Budget  FY2022Budget    SelectedYearsBudget    LifetimeBudget

HR      $200,000        $200,000      $210,000        $610,000               $2,000,000

IT      $300,000        $300,000      $310,000        $910,000               $3,000,000

And so on. If the user adds the FY2023Budget column, SelectedYearsBudget changes to reflect the new column in its total. Further, SelectedYearsBudget will be used as a filter (we don't care to see any items where SelectedYearsBudget is 0).

Doing this as a single pivot table is ideal, but if intermediate pivot tables are needed, or some VB coding, or a display page pulling values from the table with LOOKUP or other reference functions is needed, that is okay - my current solution is a Frankenstein's Monster of all of the above (except VBA) with the problem being that the intermediate table doesn't update when underlying filters change.