How to Filter PivotTable Rows by Spent Time and Show Estimates in Excel
Question details
The user wants to filter PivotTable projects by the time spent by a team, while simultaneously displaying the estimated time totals for those specific tasks.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating advanced PivotTables using the Data Model to analyze project time tracking, specifically comparing spent time versus estimated time across related tables.
- Observed behavior
- Implicit measures fail to properly filter across the related Billings and Tasks tables, requiring the use of explicit DAX measures to accurately align spent time with estimated totals.
Ensure that your Billings and Tasks tables are properly loaded into the Excel Data Model and that you have enabled the Power Pivot add-in to write custom DAX measures.
Use TREATAS to Create a Virtual Relationship
This method uses the TREATAS function to create a virtual relationship, allowing you to accurately transfer the filter context from the Billings table to the Tasks table.
Using explicit DAX measures instead of implicit ones ensures more reliable filtering in your PivotTable. The TREATAS function effectively bridges the gap between tables without requiring a permanent physical relationship in the Data Model.
Navigate to the Power Pivot tab and click 'Measures' > 'New Measure'. Define your spent time measure as: Time Spent := SUM(Billings_Table[SPENT])
Create a second measure using TREATAS to link the task IDs virtually: Time Estimate := CALCULATE(SUM(Tasks_Table[ESTIMATE]), TREATAS(VALUES(Billings_Table[TASK_ID]), Tasks_Table[TASK_ID]))
Insert both newly created explicit measures into the Values area of your PivotTable. This will correctly display filtered spent time alongside the corresponding estimated totals.

Use CROSSFILTER for Bidirectional Filtering
Alternatively, you can use the CROSSFILTER function to force bidirectional filtering on an existing physical relationship between your tables.
Manage and Analyze PivotTable Data with WPS Office
While advanced DAX Data Modeling is a feature specific to Microsoft Excel, WPS Office provides robust standard PivotTable functionalities that handle complex data summarization effectively. WPS Office is a free, lightweight, and highly compatible alternative that seamlessly handles your standard spreadsheet files and PivotTable reporting.

Frequently Asked Questions
Why should I use explicit measures over implicit ones in PivotTables?
Explicit measures (written using DAX) give you complete control over how calculations are performed and filtered across multiple tables in the Data Model. Implicit measures (simply dragging a field to the Values area) rely on default behaviors that often fail to calculate correctly in complex multi-table setups.
What does the TREATAS function do in this scenario?
The TREATAS function applies the result of a table expression as filters to columns from an unrelated table. In this context, it takes the filtered Task IDs from the Billings table and applies them to the Tasks table to calculate estimates accurately without a physical relationship.
When should I use CROSSFILTER instead of TREATAS?
Use CROSSFILTER when a physical relationship already exists between your tables in the Data Model and you simply need to temporarily change the cross-filtering direction (such as setting it to bidirectional) for a specific calculation. If no relationship exists, TREATAS is the preferred method.
Can I filter PivotTable rows using standard slicers instead of DAX?
Yes, for simpler single-table datasets, you can connect slicers to standard PivotTables to filter rows easily. However, for multi-table data models requiring specific cross-filtering behavior without altering the entire model's default relationships, DAX measures are necessary.




