Sep 01 2021 03:50 AM
I'm relatively new to PowerPivot and Pivot Tables so I'm sure I'm making some rookie error here but can't find an uncomplicated or clumsy way of doing this...
I have a set of Data. It includes the following:
1. List of around 200 SKUs with individual part numbers. Currently in Column A, with the Column name "Product in Cell A1".
2. List of accounts. Currently In Row 1.
I have recreated it here with a simple example in a screenshot.
The table shows the products that each account has purchased.
I want to be able to create a slicer with the name of each account in it, and when the button in the slicer for that account name is selected it filters out the other accounts and shows me just what products that specific account has bought. So, using the attached example, selecting the Account 3 button from a slicer shows me nothing but the quantity in the populated cells in column D along with the product code alongside it. I can deal with having empty cells if need be, but ideally want them gone. This way I can allow my colleagues to see what each specific account ordered without trawling through endless tables.
At first I thought it was simple, but when I get into it I end up with half of what I want. I can create a slicer for everything in the table, but the account name - I assume because I've got them in the row along with the product. I guess this may well be the main error but I can't see a way around it.
I'm sure that anyone remotely experienced on here will look at this and figure out how I've done it wrong instantly, but I can't see the wood for the trees. If I swap the rows and columns, having the SKUs in Row 1 and accounts in Column A, I'm going to end up with the same issue, except that I'll have the other bits of data I am missing, but still be unable to get the slicer I want.
I can't believe I can't do this in Excel so if anyone can explain the correct way to lay out the table to achieve what I need I'd be grateful. Hopefully I have explained it clearly.
Thanks!
Matthew
Sep 02 2021 11:46 PM
Screenshots work only if you're asking help on formula syntax or to ask someone to check why a formula seem to be not working.
You are asking for assistance in regard to power pivot more complicated. If you want to keep your problem a secret that's up to you, people on here do not like to try to solve puzzles especially complicated ones with just a screenshot because it becomes frustrating and time consuming and lots of back and forth questions etc etc etc. Please share your dataset.
Sep 03 2021 12:26 AM
Sep 03 2021 04:53 AM - edited Sep 03 2021 05:01 AM
Solution
See attached for sample data model with slicer
also here's a tutorial about Power Pivot and DAX formulas:
Sep 03 2021 06:49 AM
@Yea_So Wow - A whole load of info here. THANK YOU!
Sep 03 2021 06:53 AM
Sep 03 2021 04:53 AM - edited Sep 03 2021 05:01 AM
Solution
See attached for sample data model with slicer
also here's a tutorial about Power Pivot and DAX formulas: