Forum Discussion

darronsmom's avatar
darronsmom
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 the best way to get this, but I can't figure out how to get more than just the product and the main category  (in my sample, that would be the Apple and Fruit.  I don't know how to show the subcategories in the same column or row as the product.  I'm not super picky about the format, but it does need to operate that way.  I have about 50 products, split between 5 categories, and 8 subcategories for each.  How can I get them to to show in a nice neat format?

 

Thanks in advance

BI & Data Analytics
Need Help
  • Haytham Amairah's avatar
    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

    • darronsmom's avatar
      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
      • Haytham Amairah's avatar
        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