Forum Discussion

Copper Contributor

Oct 20, 2018

need help showing multiple values based on a drop down

Hi all, I need to send out a spreadsheet where a user can see a product, product category, and all subcategories for that item (see attached for what I have in mind). I think a pivot table is th...

BI & Data Analytics

Haytham Amairah

Silver Contributor

Oct 20, 2018

Hi Marlise,

To be able to summarize this data in a Pivot Table, you must fill down the values in columns: Product and Category as the below screenshot.

To learn how to fill down quickly, please watch this video.

You also can use Power Query (aka Get & Transform in Excel 2016 and above) to do so, and this is demonstrated here.

Hope that helps

Products Table.xlsx13 KB

darronsmom
Copper Contributor
Oct 22, 2018
Hello Haytham, Thank you for your response. I am actually not looking for the sum, or count- I'm looking for the product to display the way it is in my attachment. If a product is an apple, I need it to display both Fruit and Red and Green. I don't care if it's in the same column or different columns, but I need to display both subcategories. Does that make sense? Thanks Marlise
- Wyn Hopkins
  MVP
  Nov 02, 2018
  Hi Marlise
  
  Bit late to this conversation but I think the trick you need is to go to Design > Report Layout > Show in Tabular form
  
  Then next to that button is Subtotals > Do not Show
- Haytham Amairah
  Silver Contributor
  Oct 23, 2018
  Hi Marlise,
  
  Sorry, but it's still not clear enough!
  Anyway, can you provide a sample of your original data which include all the categories and subcategories?
  
  But, if you like to report the data to someone else, it's common to report a summary of this data as I did in the previous reply using Pivot Table, not the raw data itself!

Resources