Best way to set up data for visual reports (dashboard)

Hi I'm looking for a little guidance on what's the best way to set my data up so that it clear and easy to read. I have been asked by my boss to provide something that will show all data on one page that's printable and with a few graphs, I can set the graphs up with pivots on another tab (dashboard) but I have hit a block when deciding the best way to layout the data,

any guidance would be very appreciated


ps tab 1 is what I was giving to work with , tab 2 is what I went with but it does not fit to one page for printing and not easy or clear to see the requirements


Thanks
D

You have an excellent layout for what you want to do. I have two main suggestions.

1. Convert the input data into an Excel table. Excel tables have a lot of advantages, but the one that is most important here is that you can use column header names in the formulas that makes them a lot easier to read.

2. Take the calculated fields out of the source data. This will simplify the data entry. Do the calculations on the dashboard.

One assumption I made is that the Categories are unique meaning that you won't have a Face to Face Filtrate in Face to Face Assignments and in some other Type. If this should happen in the future, your data is set up to handle it, but it will take adding an extra condition to the SUMIFS commands.

I entered in "canned" data to make checking the results easier. The results, I am sure, look ridiculous.

The main formula for the quantities is =SUMIFS(Table1[Amount],Table1[Month],C$3,Table1[Category],$B4) - Note that I have "frozen" the row to look up the month and frozen the column to look up the Category. So these formulas can be copied and pasted to all the quantity cells on the dashboard.

There are some quantity cells where you need to do calculations and I leave it to you do figure out the percentage calculations.

Red means I haven't got a clue what you want for a calculation, orange means I'm not sure, but I took a guess and yellow means I'm pretty sure, but check anyway.

This should get you a good way along. This only works as well as it does because your initial data is almost spot on. You did a good job with it.

Sheet 1 is a variation on the report using a pivot table. Because your data is so well organized, it is easy to make pivot tables from it. It was so easy that I did it. There is a helper column in the source data to separate agency provided vs. in-house services. I did it crudely, but you can have a list by category and do a VLOOKUP.

What chicgolarson said and one more tip re: Pivot Tables. Never underestimate the power of helper columns in the source data. For example, you may want to show a rolling 30-day status for something. Rather than individually selecting 30 days in a filter or slicer, you can have a helper column =MyDate >= Today() - 30 - this will evaluate to TRUE for the last 30 days and can be used as a filter or slicer.

Also, sometimes pivot table charts can't be made to do exactly what you want them to do. In that case, you might have to overlay the results with named dynamic ranges and use these names as data sourced in a "regular" chart. This is a somewhat advanced topic, but here are a couple of links when you are ready for them.
http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
http://www.utteraccess.com/wiki/Dynamic_Charting

I made two columns with identical data (values). If you right click on the second column, one of the options is to "Show Values as" I selected "Percent of Column Total".