Use an Excel Data Model Measure to Sum RAM Across Related Hosts and Tomcat Tables
Question details
The user needs to fix a PivotTable that repeatedly shows the same RAM value when Hosts and Tomcat tables are related through a shared HOST field by using a Data Model measure.
- Product
- Microsoft Excel Data Model / Power Pivot
- Device & OS
- not provided
- Scenario
- The user is building a PivotTable from related Hosts and Tomcat tables and wants the RAM from the Hosts table to sum correctly for each Tomcat record.
- Observed behavior
- The PivotTable repeatedly displays the same RAM value instead of summing it according to the Tomcat relationship.
Before creating the measure, confirm that both Hosts and Tomcat are formatted as Excel Tables, that the HOST columns contain matching values with no trailing spaces, and that both tables have been added to the same Data Model.
Create a Cross-Filter Measure in the Excel Data Model
Use this when both tables are already loaded into the Data Model and you need the many-side Tomcat table to filter the one-side Hosts table.
The repeated RAM value occurs because the default relationship filter direction only lets the one-side Hosts table filter the many-side Tomcat table, not the reverse. A CALCULATE measure with CROSSFILTER(..., BOTH) makes the filter context flow in both directions.
In Excel, click Data > From Table/Range for each table, or select Power Pivot > Add to Data Model, so both Hosts and Tomcat appear in the Data Model.
Open Power Pivot > Manage Data Model, switch to Diagram View, and drag Hosts[HOST] to Tomcat[HOST] to create a relationship based on the shared HOST field.
In the Data Model, select the Tomcat table, create a new measure, and enter: Tomcat RAM := CALCULATE(SUM(Hosts[RAM]), CROSSFILTER(Hosts[HOST], Tomcat[HOST], BOTH)).
Click Insert > PivotTable > From Data Model, add Tomcat fields to Rows, and place the Tomcat RAM measure in Values so the RAM sum is evaluated in the Tomcat row context.
Verify Relationship Direction and HOST Field Consistency
Use this first if the Data Model is already set up and you want to rule out mismatched keys or relationship direction issues.
Use WPS Office as a Free Alternative for Everyday PivotTable and Spreadsheet Work
Data Model measures and Power Pivot relationships are Microsoft Excel-specific; WPS Office is a free, lightweight Office alternative that preserves .xlsx and .xlsm compatibility and covers everyday PivotTables, formulas, and formatting without a subscription.
Frequently Asked Questions
Why does the PivotTable show the same RAM total for every Tomcat row?
Because the relationship filters only from the one-side Hosts table to the many-side Tomcat table by default. The RAM measure evaluates in the Hosts context and is not affected by Tomcat rows unless you force bidirectional filtering with CROSSFILTER(..., BOTH).
Do I need Power Pivot to create this measure?
Yes. In Microsoft Excel, use Power Pivot or the Data Model to add both tables, create the relationship, and define the CALCULATE measure. Classic worksheet PivotTables cannot create Data Model measures.
Can I fix the repeated RAM value without a Data Model measure?
If the tables can be joined in one worksheet, add a helper column with SUMIFS or INDEX/MATCH to look up the RAM value from the Hosts table, then use that helper column in a standard PivotTable.
Does WPS Office support Excel Data Model measures?
WPS Office focuses on classic spreadsheet and PivotTable work. For editing Data Model relationships and Power Pivot measures, use Microsoft Excel; for daily compatible spreadsheet work, WPS Office is a free alternative.
