• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Slicer and Formula help

Wai-Chung

New Member
Good Day Everyone,

I am working on a spreadsheet where I have monthly revenue and qty purchased on 2 separate pivot table. My spreadsheet also include information such as different Territory Managers and Region. I would like to create a dashboard where I can select the "revenue" or "qty" according to TM or by Region for a given month and be able to compare with prior year total.

I was able to kind of create a current year ytd and a prior year ytd table to extract the data. On the dashboard itself, i was able to create a list box of month and TM and Type, but I didn't know how to do it for Region. So I used a slicer that connected to the 2 pivot tables.

It seems to work for the East Region by showing only the East customers, but when I select the West Region, it also include some East customers as well. Moreover, on my YTD tab, I am unable to get the correct Prior Yr Total for some of the customers (Whistler Automotive) even thought the amount, $232,692 is on the prior yr total column for them.

I have looked at the formula, but I cannot see where I am in error, so I am hoping that someone can find my error to correct me. In addition, I would like to know why my slicer is showing different results for the East and the West Region.

Lastly, I am hoping to learn whether there might be a more streamline way of doing this dashboard. I can created multiple sheets to get to the dashboard, but it seems like I might be duplicating some of the steps. Can someone please kindly review and advise so that I won't make similar mistakes in the future.

Thank you to the people on this forum for helping me in the past and I look forward to your replies.

Thank you.

Chung
 

Attachments

  • tp-2a.xlsx
    549.1 KB · Views: 21
It all looks very complicated so I'm going to address your question about streamlining.
First, since the data isn't there I would add a column J to the master data sheet (which you've conveniently hidden and protected from unhiding) headed TM, in which I'd put the formula:
=INDEX(Table1[[#All],[TM]],MATCH(C2,Table1[[#All],[Account Name]],0))
This gives you a TM name for each customer.
Then for your pivot tables I would include this new column as part of the data source.
I've hijacked your dashboard-1 sheet to get a copy of the master data sheet and put a pivot table beside it in the vicinity of cell M1, currently showing Doug Preston's 2014 (using Last Year as the Date filter in the Month field) revenue data.

Any use?
 

Attachments

  • chandoo225936tp-2a.xlsx
    776.4 KB · Views: 20
Thank you for your suggestion p45cal. I didn't know how to add using index/match previously, so I added using vlookup from my Names List. I think it still work. Now that I have the extra column, I think it has helped my worksheet. it is still a work in progress because I have to find a way to modify the formula calculations when I group my months into years.

Thank you for your help,

Chung
 
Back
Top