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
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