+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Forum Contributor
    Join Date
    11-26-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    334

    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
    Attached Files Attached Files
    Last edited by dougers1; 02-12-2019 at 10:27 AM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,962

    Re: whats best way to set my data up - for visual reports(dashboard)

    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.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    11-26-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    334

    Re: whats best way to set my data up - for visual reports(dashboard)

    Thanks this helps a lot

  4. #4
    Registered User
    Join Date
    12-06-2017
    Location
    Chicago, IL
    MS-Off Ver
    2016
    Posts
    22

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

    I have just finished designing a big dashboard for a client, and learned a lot in the process. Hope this is helpful to you.
    Tip - as the previous poster mentioned, you can easily set up pivot tables that will populate with your data. That being said, dashboards are really intended to provide a primarily graphic representation of data that can provide executive summary information that will give summary data that can be digested quickly. More detailed information can be provided below the graphics, or as backup reports that can be drilled into for further analysis.
    In my experience, the easiest way to do that is to create "mini-pivots" that will feed your graphics, while leaving the primary pivot on the lower section of the primary dashboard screen. There are a couple of reasons I recommend that. first, the mini-pivots can be connected to slicers and timelines that will allow you to slice and dice the data in a variety of ways to narrow or broaden the amount of data being reviewed at one time, while maintaining the primary pivot in the dashaboard. (if charts are linked to the primary pivot, it will change along with the graphics when you are drilling into details, and that is not generally what I would recommend.) The second reason using mini-pivots is that I have generally found that charts and graphs look better if the data in the table is pivoted (which allows the axis on the charts to pivot). This makes the base table very difficult to read. So - put your pivots on background tabs that you can hide.

    I've taken the liberty of providing a couple of suggestions in the attached. I had a bit of difficulty interpreting your data and determining what metrics might be important to you, but hopefully I've given some examples that you can make sense of and build on.

    Good luck. I think dashboards are lots of fun!
    Attached Files Attached Files

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,962

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

    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

  6. #6
    Forum Contributor
    Join Date
    11-26-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    334

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

    how did you get the % in the pivot on the suggested tab ?

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,962

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

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

  8. #8
    Forum Contributor
    Join Date
    11-26-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    334

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

    aha - Thank you

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    17,486

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

    If you are looking for a few more example ways of showing your data, click on the Dashboard button at the top of the screen. Here are some examples of what was produced in the competition held by this forum in 2014.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1