ODBC Excel多层数据透视表差异化计算（SUM/MAX/SUM）需求问询

Absolutely, this requirement is totally achievable! Since regular pivot tables lock you into a single aggregation per value field, we’ll use either Power Pivot (the recommended, scalable approach) or a manual workaround to get the tiered calculation logic you need.

推荐方案：Power Pivot + DAX度量值 #

Since you’re using an ODBC data source, Excel can easily pull this data into the Power Pivot model, where we can build a custom measure that automatically switches aggregation based on the pivot table level:

1. Import your ODBC data into Power Pivot

   • Go to the Data tab → Get Data → From Other Sources → Select your ODBC connection, then load the data into the Power Pivot model.

2. Create a custom DAX measure
   In the Power Pivot window, go to the Measures tab and create a new measure with this formula (replace YourTableName with the actual name of your table):

   WarehouseDaysAggregation = 
   VAR AtDateLevel = ISINSCOPE('YourTableName'[Date])
   VAR AtStockCodeLevel = ISINSCOPE('YourTableName'[StockCode])
   VAR AtBinLevel = ISINSCOPE('YourTableName'[Bin])
   RETURN
       IF(AtDateLevel, SUM('YourTableName'[NoOfDays]),
           IF(AtStockCodeLevel, SUM('YourTableName'[NoOfDays]),
               IF(AtBinLevel, MAXX(VALUES('YourTableName'[StockCode]), CALCULATE(SUM('YourTableName'[NoOfDays]))),
                   SUMX(VALUES('YourTableName'[Bin]), MAXX(VALUES('YourTableName'[StockCode]), CALCULATE(SUM('YourTableName'[NoOfDays]))))
               )
           )
       )
   

   Here’s what this does:

   • At the Date level (most granular): Returns the individual NoOfDays value (which is 1, as you noted)
   • At the StockCode level: Sums all days for that stock code (matches your requirement for SUM here)
   • At the Bin level: Takes the MAX of the summed values from all stock codes in that bin (so 10001A shows 22, as needed)
   • At the grand total level: Sums the MAX values from each bin (matches your total SUM requirement)

3. Build your pivot table with the measure
   Drag Bin, StockCode, Date into the Rows area, then drag your new WarehouseDaysAggregation measure into the Values area. The pivot table will now automatically apply the correct aggregation for each level.

备选方案：普通透视表手动 workaround（适合静态数据） #

If you can’t use Power Pivot, you can manually adjust the pivot table for one-time use (note: this won’t auto-update if your data changes):

1. Build a regular pivot table
   Set NoOfDays to use SUM aggregation—this will get you the correct StockCode-level totals (like 6 for SANK1313).

2. Switch to classic pivot layout
   Right-click the pivot table → PivotTable Options → Display → Check "Classic PivotTable layout" to make individual summary cells editable.

3. Manually update Bin-level totals
   Find the Bin-level summary cells (like the 44 for 10001A) and replace them with the MAX value from the StockCode totals under that bin (22 in your example).

Keep in mind this workaround is fragile—you’ll have to re-edit the totals every time your data refreshes.

内容的提问来源于stack exchange，提问作者Steven de Beer