Try to beat the limitations of Pivot Tables

[SimonHow]

I am being beaten by what seems to be the limitations of pivot tables "out of the box", and after extensive searching without hitting on a solution, I am therefore trying to build a work around.

I have a large table the I have summarized into a pivot, the result looks a bit like this:

Sum of Sales Qty	Column Labels
Row Labels	TS15	TS15/10	Y.BTS15/05	Y/TTS15	YTS15	Total
03/01/2023	88300​					88300​
04/01/2023	80100​					80100​
05/01/2023	47200​					47200​
06/01/2023	92200​					92200​
09/01/2023	77400​					77400​
10/01/2023	44300​					44300​
11/01/2023	36500​				28800​	65300​
12/01/2023	50100​			4800​		54900​
13/01/2023	188300​					188300​

This is essentially gathering quantity data and summing according to the date of a transaction. There are likely several transactions in a day, and I only need the total for the day. I use a slicer to select which products are included in the column labels, so the content and therefore width is dynamic.

I now want to perform some simple min, max and average type calculations. However I can't embed these into the table calculation methods as a want the result to be average per day, not true average of all transactions, for example.

I can do this outside of the pivot table, but cant find a way to get the range used for the calculation to be dynamic. i.e. if I select a different set of products, the total column might slip from Column G:G to Column J:J. So If I can find a way to define a dynamic range that always refers to the column "Total", irrespective of where it is on the sheet, or how long it is, then I am home and dry.

Am I asking too much of excel ? - all help gratefully received

Many thanks in advance

 

[RoryA]

You are asking too much of a normal pivot table as you effectively want different calculations at different levels, and it won't do that. You need to either use the data model and Power Pivot, or use Power Query to aggregate your data to a per day level and then build a pivot table from that.

 

[SimonHow]

Thanks, I don't mind if I perform these calcs outside of the pivot table, if I can have a dynamic range that captures the range of the totals column, wherever that is on the sheet. Is that at all possible ?

 

[RoryA]

You can use INDEX and MATCH as long as the header row doesn't change. Depending on how/where you want the results displayed, you may be better off biting the bullet and using one of the approaches I suggested.