+ Reply to Thread
Results 1 to 4 of 4

Using pivots and pivot charts for interactive dashboards

  1. #1
    Registered User
    Join Date
    02-23-2018
    Location
    New York, USA
    MS-Off Ver
    2013
    Posts
    2

    Using pivots and pivot charts for interactive dashboards

    I'm struggling to do a few things with regards to developing actionable insights and presenting in an easy to use dashboard for a wide user group.

    The situation is as follows;

    I want to Create a hierarchy dashboard across a number of kpis. I.,e start at the president's view of the average across a region, with the ability to drill into different parts of the region, or by type etc.
    The dash board would be for the performance of a a number of restaurants (~1000) across a number of metrics:

    Characteristics of each restaurant that I want to capture:
    - Restaurant type (i.e., Standalone, Mall, etc)
    - Annual sales volume (i.e., group by sales ranges)

    KPI"s that I want to measure:
    - Operating margin %
    - Food, Paper, Labor %
    - Drive Thru Times
    - Customer feedback incidents per 100 transactions
    - Training completion %

    I have all of this data available, my question is twofold:

    1. What insights can I gather from the above that will lead to action. I.e., If I have 500 normal restaurants, with an average operating margin of 10% but a range of 8 - 15%, how can i present the data in a dashboard format that will allow the president for example to see the whole view and easily understand where the outliers and problem areas lie. And similarly, if a problem here is identified, how can I easily pair up what might be driving this from the data available. I.e., a scan of the other metrics to assess for abnormalities

    2. What is the best way to present the data for A) visual ease, and B) user friendliness (i.e., all users can easily navigate to helpful information for their own use)

    Many thanks!

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

    Re: Using pivots and pivot charts for interactive dashboards

    Dashboards such as this are typically built on pivot tables. You say that you have all the data you need, but is it in the right format. For additional flexibility, you may want to consider adding helper columns to your source data. For example, you may want to show one analysis where metric A is less than 10% and another where metric A is 20%. You can have a cell and give it a name like "Cut_Off", throw some data validation so you can only add a decimal number between 0 and 1 and format it as a percent. Then, use a helper column with the formula:
    [Metric A] < Cut_Off. This will evaluate to True or False and you can use the helper column to filter the pivot report.

    Another tactic is to use named dynamic ranges and overlay them on pivot tables or other data layouts to create dynamic charts.

    I really cannot recommend much more without seeing the kind of data you have and how it is organized.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    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
    Registered User
    Join Date
    02-23-2018
    Location
    New York, USA
    MS-Off Ver
    2013
    Posts
    2

    Re: Using pivots and pivot charts for interactive dashboards

    Thank you Dflak,

    See attached sample data for the dashboard, and my initial thoughts of what it should look like. (Obviously the pictures of the graphs are purely for illustrative purposes, i just wanted to show what the overall page should look like).

    Few points to note and to highlight the requirements I'm building towards:

    Requirements:

    1. I want to build a dashboard for the key metrics outlined in the spreadsheet attached (desired dashboard tab - traffic/cheque, dt times, operating margin, etc)
    2. The dashboard must have the ability to summarize and get granular - i..e, if the vice president want to see how things are performing across his region he should see an average for each of his reports, if he drills into those reports he should an average across all of their reports, and so on until he can drill down to a single restaurant level
    3. The dashboard should be analytical powerful yet user friendly and subtle - i think the real value will come from excellent presentation of the data, ease of navigation to slice by different regions and views such as store types, and the ease of highlighting outliers and trends in the data - i.e., poor performers, good performers, good/poor trends and perf vs same time last year.
    4. The dashboard should have the ability to scale up - this is a small sample of data, for ~400 restaurants, the final file would have ~ 8000 restaurants

    Notes:

    1. I've prepared overviews for single metrics from the above, i.e., profitability by store, using pivot tables and a pivot line chart. This gave a good view of point in time performance by owner group, but what it was missing was the dynamism to span across a number of metrics and on a much larger scale, and it also didn't really deliver any insights, i..e, profitability might be low, but hard to understand why - hence including these metrics i thought would give a good opportunity to highlight root causes in a graph and simplify the process for the vice president right down to a manager responsible for a number of restaurants.
    2. Of the available, i don't have historic data currently, but if you want to mock up a sample of illustration purposes I can then plug some real data in if it works. Obviously I'v scrubbed this data, so ignore if any of it looks funny to you. of the data I have available, the frequency at which it updates varies per below;
    - Annual sales range = Annually
    - Food/paper/labour = Monthly
    - Operating Margin = Monthly
    - Avg cheque and transactions = weekly and last year (2017) comparison
    - Drive thru times = weekly
    - Guest feedback = weekly
    - Training completion = weekly
    I'm a little unsure of how to best build up trend analysis for this. Maintain weekly data and after a few weeks i;ll have a trend and continue to do this and after a few months I'll have quarterly trends and so on? Open to your thoughts on this.


    That's what I'm thinking in a nutshell, would love to hear your thoughts!
    Attached Files Attached Files

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

    Re: Using pivots and pivot charts for interactive dashboards

    I took a quick look. Some good news and some bad news.

    The good news is that the data look like they are in a format suitable for analysis. Also, I am impressed that you have it in an Excel table. That will save me from having to do it and having to explain what Excel tables are .

    There are a couple of potential issues I see.

    One is annual sales - there is an implied range. If you want to do an analysis on this, these figures need to be pulled apart into a minimum and maximum. We'll probably need some VB code to translate 2.0M - 2.5M to 2,000,000 and 2,500,000 and < 600k to 0 and 600,000. This is do-able.

    The bigger issue is that most of the data are percentages and averages. These are impossible to aggregate. For example you may have 9 out of 9 items for one store (100%) and 0 out of 1 of the same item for another store (0%). Collectively this should be 9 out of 10 or 90%, but a the average of 100% and 0% is 50% - a totally misleading figure.

    So collecting data by store type or store owner or any other grouping will be a problem. You will need to get the raw quantities that go into these calculations to be able to do a proper analysis. Do you have access to this data? Talk to the provider of this report.

    Unfortunately, without the basic "ground level" data, there is very little that can be done with this report.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Best way to set up data for future analysis - Pivots, Dashboards...
    By JLSFO in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 09-20-2016, 03:07 PM
  2. Designing dashboards (donut pie charts)
    By colegerald38 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-10-2016, 08:17 AM
  3. Need Help with Pivot Table and Charts for Dashboards
    By jayeshk in forum Excel General
    Replies: 4
    Last Post: 12-18-2015, 05:49 AM
  4. Dashboards limitations (made of pivots/charts + vba)
    By Bishonen in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-07-2015, 05:56 PM
  5. [SOLVED] Interactive Charts/Dashboards
    By dspblues in forum Excel Charting & Pivots
    Replies: 38
    Last Post: 01-22-2015, 09:36 PM
  6. small enquiry about dashboards and interactive charts..
    By ZOH in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-15-2015, 07:57 PM
  7. [SOLVED] Insert Slicer for Pivots Only or can we use it for Charts without Pivots?
    By eldwardo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2013, 04:27 AM

Tags for this Thread

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