The pivot table that disagrees
A sales director connects to the published Sales FY24 dataset using Analyze in Excel. Her pivot table has Product Category on rows with a distinct-count Customers measure and Sales Amount. It returns 7 categories and 5,006 customers. The published report page shows 9 categories - the two extra have blank Sales Amount - and 4,812 customers. The dataset has RLS with a Country Managers role. The director is a Member of the workspace. The report page carries a page-level filter excluding the Internal channel and a visual-level Top 10 filter on a separate visual. The director is convinced she is seeing regions she should not.
1. Account for the difference in both numbers and for what the director sees.
Read the worked answer
The pivot and the report are answering different questions.
- Category count, 7 vs 9. Excel PivotTables apply auto-exist: row items whose measures are all blank are dropped from the result. The two categories with no FY24 sales never appear. The Power BI visual issues the full member set from the dimension and shows those categories with blank measures. Same model, different presentation.
- Customer count, 5,006 vs 4,812. The report inherits the page-level filter excluding the Internal channel. Analyze in Excel opens the dataset - no report, page, visual, bookmark or Top N filters apply. The pivot queries the whole model, so it is larger.
- The region question. The director is a workspace Member. Admins, members and contributors hold edit permission on the dataset and are not restricted by RLS, whether they open the app, a shared link or Analyze in Excel. RLS is enforced for consumers: viewers, app audiences, and users with build permission outside the workspace. She sees every region because of her workspace role, not because of the pivot.
Before changing anything, establish which number the business should see. Here the report is right about scope and the pivot is simply unfiltered.
2. How do you prove that explanation rather than assert it?
Read the worked answer
Isolate one variable at a time.
- Add Channel = Internal to the pivot as an explicit exclusion filter and rebuild. If it falls to 4,812, the gap was the report filter.
- In the report, turn off the visual Top 10 and switch the matrix to show items with no data. The two blank categories appear - the same set the pivot suppresses by auto-exist.
- In the service use Test as role for Country Managers with the director's UPN, then sign in separately with a viewer account that is not a workspace member. If the viewer sees one region and the director sees all, the workspace role is the cause.
For a permanent check, write the DAX once - EVALUATE SUMMARIZECOLUMNS(...) with and without the channel filter - and run it in DAX Studio against the dataset. A number you can reproduce from a query is a number you can defend.
3. What do you change, and what cannot be fixed?
Read the worked answer
Change:
- If excluding the Internal channel is a business definition rather than a report preference, move it into the model - a measure that removes Internal, or a row filter on the sales table - so every consumer, Excel included, gets the same scope.
- Fix the permission model. If RLS is meant to apply to the director, she should consume the app as a viewer or through an audience; IT keeps member roles in a workspace that holds no data for end users.
- Document that Excel suppresses blank-measure row items by auto-exist, so nobody reopens this as a bug next quarter.
Cannot be fixed:
- Analyze in Excel never inherits report-level filters, bookmarks or visual Top N, because it connects to the dataset rather than the report. Either replicate the filter in the pivot or push the rule into the model.
- RLS filters rows, not objects. It cannot hide a table or a column. If any consumer must not see payroll or cost, that is object-level security via XMLA / an external editor, or a separate dataset.
The durable answer is to document the two consumption paths side by side: a pivot is a raw model view, a report is a curated view.