• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table filter shows all values, but doesn't display them

I have a pivot table filter that shows multiple values in a particular column. But when I look at that column in the source data, I only see one value. Why does the pivot table filter show more than one value when the source data has only one value?
 
Last edited:

Chihiro

Excel Ninja
Depends on how your pivot table is built.

If it's based on data model and filter is from dimension table. By default, it will show all values available in dimension table, and not just the ones available in fact table.

Also, if it's in filter field, it does not change based on any other slicer/filter applied. It will show all values available in source table.

If you need further help, I'd recommend uploading desensitized mock-up workbook that demonstrates your issue.
 

GraH - Guido

Well-Known Member
You might want to look at this in your pivot table options.
If at one point in time there were more values in the column, depending of these settings they are memorized in the pivot cache.
60847
 
Depends on how your pivot table is built.

If it's based on data model and filter is from dimension table. By default, it will show all values available in dimension table, and not just the ones available in fact table.

Also, if it's in filter field, it does not change based on any other slicer/filter applied. It will show all values available in source table.

If you need further help, I'd recommend uploading desensitized mock-up workbook that demonstrates your issue.

You mention it depends on how the pivot table is built. I always thought there was one way, but apparently, there is more going on in the background.

1. What are some ways a pivot table can be built.
2. Regarding the terms, data model, dimension table and fact table can you point me to answer websites that talk more about these terms?
 

Chihiro

Excel Ninja
1. From Excel Table/Range, From External Connection, From Table but with Data Relationship and loaded to model, Data loaded from PowerQuery to Data Model etc.

2. Have a read of...
https://support.office.com/en-us/article/create-a-data-model-in-excel-87e7a54c-87dc-488e-9410-5c75dbcb0f7b

However, your issue, from the sound of it, isn't Data model related. As you are not familiar with it. More likely cause is what @GraH - Guido indicated.
Or due to date hierarchy being used.
Thank you.
 
Top