•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to filtering additional fat sets along with a pivot table
Our take
If you're looking to filter additional datasets alongside your existing pivot table, it’s essential to establish proper relationships between the data. Start by ensuring that all datasets share a common field, such as salesperson names, to facilitate filtering. When encountering duplicate issues, review your data for any inconsistencies that might cause duplicates, even if they originate from your customer software. Consider using unique identifiers or consolidating your data before creating relationships. This way, you can effectively leverage slicers to filter multiple datasets seamlessly.
I have a few different data sets of sales based on different time periods and sorted by salesperson. I have a pivot table based on data set 1. The other data sets are also by salesperson but different time periods. How can I get the additional data sets to filter alongside the original data set as it’s filtered by salesperson? I tried adding a slicer but couldn’t get the relationships to work because t said I have duplicates. I don’t know why any duplicates would exist because the data is pulled difectly from our customer software system. Any suggestions?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How do I get slicers to work with a pivot table with multiple value tables, and also displayed in an easily legible way?I have an example list of fifty staff names with six different types of information for each staff member. Three of those criteria (Role, Types of Exp., and Clients) have multiple values, e.g. a staff member will have worked with multiple clients, have many types of experience, and have played different roles. My goal is to create a table of the staff that can be easily filtered according to any of the six criteria, including being able to filter by multiple values for a single criteria, e.g. search for staff members who are in the Chicago office with a bill rate of 200 or less, and who have worked with Northwind and Contoso. I thought that creating a pivot table with slicers for each criterion would be a good way to do this. Steps I followed: I took my initial set of data and converted it to a table. I then created normalized data by copying the name column and each of the multiple value columns into three new sheets, and then used From/Table Range to access Power Query and transform the data into rows as a table. I then established relationships between each individual multiple value criteria table and the original staff table, using the Name column since it is what they have in common. I then created a pivot table from the data model, and added slicers for all six criteria based on the tables where those criteria live. I'm stuck at this point. With just the Name field from the Staff table in the Rows Area of the pivot table, only the Office, Yrs. of Exp. and Bill Rate filters work correctly. If I want to get any of the multiple value filters to work, I need to add the slicer based on the table for that specific criteria, and then also add the corresponding field to the pivot table from the corresponding multi value table. Unfortunately, then the information is very difficult to read due to the way the pivot table displays and nests the information. A list of 50 names turns into over a thousand rows of data. My questions are: Is there a way to get all six slicers to work with just the name field from the Staff table in the Rows area? Is there a way to also have the other criteria displayed in the pivot table in a more legible way, similar to the actual Staff table? If a pivot table and slicers are not the best way of going about this, what would be a better way? Ideally, I'd have the Staff table itself filterable with the slicers, but I'm not sure that's possible. I am new to all of this and apologize if I am sometimes not using the correct terms. Thank you! Edit: I am using Microsoft 365 on desktop. https://preview.redd.it/68q1nmv2hmyg1.png?width=490&format=png&auto=webp&s=92c147b286e378307ce5b79440612fd1ef4417ee https://preview.redd.it/rh71o8v2hmyg1.png?width=1399&format=png&auto=webp&s=15677df44967aeab7e24d2138a34e02674332d53 https://preview.redd.it/a8id0hv2hmyg1.png?width=1284&format=png&auto=webp&s=0c8ae142adee38ea0fcbef0dfe7dd8583ef5fcc8 https://preview.redd.it/1g3pp9v2hmyg1.png?width=388&format=png&auto=webp&s=76f66fb33fd59a5dc15785e07ce7c5400888f045 https://preview.redd.it/oylhz9v2hmyg1.png?width=676&format=png&auto=webp&s=e55d776248bc373d1292778c027d1f5c544429e3 https://preview.redd.it/5ff6g9v2hmyg1.png?width=2559&format=png&auto=webp&s=ca4d12eb1a125ada20ce9d1c82f28a1de1502b41 https://preview.redd.it/dxoc49v2hmyg1.png?width=445&format=png&auto=webp&s=2805ea6ebf62853610c519ac203ad5cb4266e34b submitted by /u/ToDigAndBeDug [link] [comments]
- Showing a row where no value existsI have a list of 5 countries we sell products to. And we split it into 3 product categories: internet, consulting services, phone. I have used power query to and loaded the connection into the data model, but not as a table within a worksheet. These are my total sales amount pivot tables: Filter: USA phone | 500 mill Consulting | 20 mill Internet | 30 mill Filter: New Zealand Phone | 5 mill Internet | 20 mill Problem: I need New Zealand to show Consulting and a sales amount of 0. There is no “consulting” row in the source data itself for New Zealand so “show zero values” in pivot table settings doesn’t work I figure I’ll need to create my own tables listing: Phone, Consulting and Internet as row headings. But how should I get my sales sums? I’m guessing doing xlookups using my pivot table is the data source is not best practice Do I scrap my pivot tables and load the power query as a table for sumif? submitted by /u/Interesting-System [link] [comments]
- Pivot Table top bar date filter doesn't update in chronological orderI have a an excel file which I had been building from scratch from a downloaded report every month and am trying to better automate since I do the same type of data filtering each month. I decided to create a table with the data that is linked to a pivot table which sorts and filters it into the format that I need as long as I manually adjust a couple things. However, whenever I add new data to the table, the date filter at the top of the pivot table does not show in chronological order. (Accountant working on April data and need to remove anything that was posted in May.) Theoretically I could use the timeline feature, but I'd rather not. I have resorted the source table in chronological order; I have removed the filter and re-added it in various formats. I've changed the data source, played with various settings. Is there a quick "fix" for this without manually creating the pivot table each time? I have several of these pivot tables on a sheet extracting different data sets sourced from the same table. In one of them, I need to see ONLY the items with a May date, so having to scroll through to check or uncheck the correct dates if they are out of order could lead to errors. Additionally, assuming that this can be corrected. Is there a way for an excel sheet to automatically add or remove row (by hiding or deleting, don't care which) as a pivot table gets longer or shorter so I don't have to guess how many rows I need to add before it cuts into other data below it? Sometimes I need 70+ rows and others only 30. I'm sure there's an overall better way to do what I'm doing. Thanks in advance. https://preview.redd.it/56optogx0qzg1.png?width=406&format=png&auto=webp&s=551180d29effbcfedb7038f60612e33f4e897d3d submitted by /u/inez6453 [link] [comments]
- Pivot Table Including Values Not in FilterI have data that is filtered. I then create a Pivot Table. The Pivot Table includes that cells that were not included in my filter. Is there a way I can exclude that information in the Pivot Table? submitted by /u/GlideAndGiggle [link] [comments]
Tagged with
#real-time data collaboration#Excel alternatives for data analysis#big data management in spreadsheets#generative AI for data analysis#conversational data analysis#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#cloud-based spreadsheet applications#real-time collaboration#rows.com#digital transformation in spreadsheet software#Excel compatibility#Excel alternatives#financial modeling with spreadsheets#pivot table#data sets