Pivot Table Issues

How to Filter PivotTable Rows by Spent Time and Show Estimates in Excel

Muhammad TalhaMuhammad Talha Nov 11, 2026 869 views

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.

How to Filter PivotTable Rows by Spent Time and Show Estimates
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the Time Spent Measure

Navigate to the Power Pivot tab and click 'Measures' > 'New Measure'. Define your spent time measure as: Time Spent := SUM(Billings_Table[SPENT])

2
Create the Time Estimate Measure

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]))

3
Add Measures to the PivotTable

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 TREATAS to Create a Virtual Relationship
Best Practice: Explicit measures provide much more reliable cross-table filtering than simply dragging standard fields into the PivotTable Values box.
Free Microsoft Office alternative

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.

Fully compatible with Microsoft Excel (.xlsx, .xls) formats.Powerful built-in PivotTable features for standard data filtering and summarization.Free, lightweight, and features a familiar tabbed user interface.Cross-platform support for Windows, Mac, Linux, iOS, and Android.
microsoft office alternative - wps office

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.