Hello and thank you for giving this a look.
I am wondering whether it is possible to add a filter in the attached pivot where I could select either sum of total sales or sum for any of the 5 individual company sales.
Any help you can offer will be greatly appreciated.
Best,
YevD
If I am understanding correctly, then try with a Slicer as shown in the attached. To see all sales, do not select any region. To see only a specific region, then select it. To see multiple regions, select multiple.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Hi Alan, thanks for the reply. Sorry if this wasn't very clear. I'd like to have a single filter or slicer that would have a selection for Company 1 Sales, Company 2 Sales, 3, 4, 5, & Total Sales - so when each is selected the sum of sales in the table will update accordingly.
The data as arranged on Sheet1 is not conducive to producing the output you are looking for, however as you have the 365 version of Excel you could use Get & Transform to produce a data set that is.
First convert the data on Sheet1 into an Excel table (Ctrl + t)
The following Advanced Editor code puts the data into record format:Note that the Total Sales column is removed.
Please Login or Register to view this content.
The resulting table and corresponding pivot table are displayed on the Sales Table sheet.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Apologies on a delayed response. Your code seems like almost exactly what I ended up doing - I unpivoted the company sales columns in Excel query editor while keeping total unchanged. This allowed me to create a pivot table with a single filter providing options for total and individual companies.
This message went wrong and could be deleted.
Last edited by DJunqueira; 12-02-2021 at 02:54 AM.
Just playing with the visualization of a Pivot Table.
This is exactly it. Thank you!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks