Help creating table for creating correct data layout for Power Pivot - Am I missing something?!

[BoogieNut]

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

[#2]

@BoogieNut 

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.

[#3]

I appreciate that but the dataset is loaded with confidential data; I’d likely lose my job if that were available to the public… With respect, I think that the screenshot adequately illustrates the format and layout of the data; the data itself should t really be necessary to see the problem (or my error).

[#4 · Accepted solution]

@BoogieNut 

See attached for sample data model with slicer

also here's a tutorial about Power Pivot and DAX formulas:

https://youtu.be/e-CFYi52gpc

[#5]

@Yea_So Wow - A whole load of info here. THANK YOU!

[#6]

The youtube video allows you to download their tutorial sample files.
Don't forget to mark your post as answered

cheers