Selective Data from Pivot Table to Dashboard and Chart

[nikwak]

I have been trying to get the following to work for the last week but my brain is fried and I am sure I am missing something really simple.

I need to construct a Dashboard that will pick up selective data from the data sheet and give the outcome as a line chart. The data follows the trends in ticket sales from different cities and venues around the country.

I have a sheet of data: Column A is the city, Column B is the name of the venue (some cities have more than one venue, Column C is the name of the production for which the tickets are being sold, Column D is the year of that particular production. Column E has a list of the associated potential number of tickets available for the city, venue, production and year of the previous columns and Columns F through U have a breakdown of each weeks sales in ticket numbers.

Basically I want to be able to go to a Dashboard and have 4 selective drop down lists (Columns A through D) in order that I can then have a line chart show up below with the sales showing for the selection chosen in the drop down lists. To make things (slightly) easier, if the person on the Dashboard chooses (for instance) Glasgow (Col A), King's Theatre (Col B), Mousetrap (Col C) and 2014 (Col D) the selection would correspond directly to a specific line in the data sheet (in other words it's not taking data from all different positions on the data sheet to work out the graph).

I know I have probably not made it clear and I have tried to paste a copy of a screenshot to demonstrate but can't seem to be able to do it.

Hope someone can just point me in the right direction

Nick

 

[pbornemeier]

If you can settle for a column chart, you can make a pivot chart of the data you described and set the City, Venue, Production and Year as the row labels and Tickets Available and the week data as Values for the PivotChart. When you filter the fields on the pivot chart, you will display a single row of the dataset.