{"id":570,"date":"2025-04-07T12:01:29","date_gmt":"2025-04-07T12:01:29","guid":{"rendered":"https:\/\/pressbooks.palni.org\/leveragingdatavisualizationtocommunicateeffectively\/?post_type=chapter&p=570"},"modified":"2025-07-21T22:51:37","modified_gmt":"2025-07-21T22:51:37","slug":"interactive-dashboards","status":"publish","type":"chapter","link":"https:\/\/pressbooks.palni.org\/leveragingdatavisualizationtocommunicateeffectively\/chapter\/interactive-dashboards\/","title":{"raw":"Interactive Dashboards","rendered":"Interactive Dashboards"},"content":{"raw":"
\r\n

Numbers have an important story to tell. They rely on you to give them a clear and convincing voice.<\/p>\r\n

\u2014Stephen Few<\/p>\r\n<\/blockquote>\r\n

\r\n

What Do You Think?<\/strong><\/p>\r\n

Matthew (Matt) mentioned he was competent using Excel to his boss. Matt works at XCaliber Food Distribution in the Midwest and has just been asked to provide data analytics to the owner. XCaliber Food Distribution celebrated its first year in 2024 with mixed results. Today, Matt received an exported file from the accounting software and wants to do a full analysis of last year\u2019s sales. The email from the owner indicated that the files were CSV files and represented a data dump of revenue information for the last year. Matt knew that CSV meant comma-separated values file and that he could open the file in Excel. Matt considers his Excel skills to be fairly good, but the owner asked several questions in an email that showed he wanted to move beyond a report. Instead, the owner wanted to interact<\/em> with the information in a dashboard.<\/p>\r\n

Matt reviewed the email and made notes. Here is the list:<\/p>\r\n\r\n

    \r\n \t
  1. I want to know how well my salespeople are doing. Which salespeople are selling the most; in what city, state, and region; and what are they selling? Are there two or three salespeople who could conduct the training (with pay)?<\/li>\r\n \t
  2. Are there products that should be dropped?<\/li>\r\n \t
  3. I want to know if using the summer intern in June and July was worth the cost (about $4,000).<\/li>\r\n \t
  4. Is every salesperson collecting from their customer by check, cash, or credit?<\/li>\r\n \t
  5. What is the range of invoices by customer, and who are our best customers? Are there opportunities for upselling?<\/li>\r\n \t
  6. Are we shipping out within the same day on a consistent basis?<\/li>\r\n \t
  7. Do we have regional overlap? Are two salespeople selling to the same customer?<\/li>\r\n \t
  8. I want to increase revenue to $60,000\/month in 2025. Can you do some sort of analysis to see the best way to reach this goal\u2014perhaps using a gap analysis or something like that?<\/li>\r\n \t
  9. I want to be able to ask a question and get the answer instantaneously. I heard it is called a dashboard. Can you set up a dashboard that allows me to analyze the data right along with you?<\/li>\r\n \t
  10. What do you think a survey of our customers will tell us?<\/li>\r\n<\/ol>\r\n

    Based on this list of questions, where would you start?<\/p>\r\n\r\n<\/div>\r\n

    Introduction<\/h1>\r\n

    This chapter delves into the intricacies of constructing informative and engaging dashboards, designed to communicate insights effectively to diverse audiences. We cover a spectrum of topics that includes the art of storytelling with slicers, the nuances of reports versus dashboards, techniques for creating impactful visualization, and foundational rules of dashboard design across various fields. Data no longer resides in static spreadsheets; it breathes and evolves as users explore, analyze, and draw conclusions, effortlessly<\/em>.<\/p>\r\n

    We plan to illustrate the application of these concepts along with ways to tell the story better. By the time you conclude this chapter, you will possess the knowledge and expertise to construct interactive dashboards that transcend mere data presentations. You will have the tools you need to weave compelling stories with data, unlock the insights within your datasets, and be well versed in the art of data-driven communications.<\/p>\r\n

    Chapter 4 addresses the following learning objectives.<\/p>\r\n\r\n

    \r\n

    Learning Objectives<\/p>\r\n\r\n<\/header>\r\n

    \r\n

    At the end of this chapter, students should be able to:<\/p>\r\n\r\n

      \r\n \t
    1. LO 1: Review various dashboards and evaluate best practices.<\/li>\r\n \t
    2. LO 2: Understand how to clean data.<\/li>\r\n \t
    3. LO 3: Apply the appropriate data visualization: report versus dashboard.<\/li>\r\n \t
    4. LO 4: Build visuals and dashboard to structure storytelling.<\/li>\r\n \t
    5. LO 5: Use a dashboard for gap analysis to support decision-making.<\/li>\r\n<\/ol>\r\n

      Key Terms:<\/em> cortex activity, dashboard, data cleaning, data model, dopamine, gap analysis, mirroring, neural coupling, PivotCharts, PivotTables, slicer, storytelling<\/p>\r\nDownload Chapter 4 PowerPoint (14 MB)<\/a>\r\n\r\nDownload Chapter 4 Student Files (<1 MB).<\/a>\r\n\r\n<\/div>\r\n<\/div>\r\n

      4.1 Report Versus Dashboard<\/h1>\r\n
      \r\n

      Data visualization must communicate answers and provide business outcomes. If the data feeding an analytics dashboard is not trusted, the results are meaningless. If the visualizations do not answer important business questions, the visualizations are not actionable.<\/p>\r\n

      \u2014Katie Horvath, CoreSignal<\/p>\r\n<\/blockquote>\r\n

      What is a dashboard? A dashboard is NOT a report. Reports are the most common way to communicate business intelligence: \u201cThe key attribute of a report is that it doesn\u2019t lead a reader to a predefined conclusion. Although a report can include analysis, aggregations, calculations, and event charts, reports often require the reader to apply his own judgment and analysis to the data\u201d (Alexander & Walkenbach, 2013, p. 12). A dashboard<\/strong> displays data visually and draws focus to key trends, comparisons, and exceptions. Focus is the keyword here\u2014having only relevant data is the goal. Most dashboards have a technique for the user to interact with the data. In the simplest Excel dashboard, adding a slicer can allow the user to answer additional questions, show new trends, and drill down into the data. A slicer<\/strong> in a dashboard is a user interface element that provides an interactive way to filter and analyze data displayed in various visuals or charts. Before we go much further, look at the difference between a dashboard and a report. Dashboards and reports are quite different in Microsoft\u2019s Power BI. For the purposes of this chapter, a dashboard provides quick insights and a consolidated view of key metrics to answer questions from the user. Users will often request a way to interact with the dashboard (i.e., a slicer). Dashboards can provide real-time monitoring whereas reports are more historical. We will start with a very simple example scenario.<\/p>\r\n\r\n

      Campus Travel Scenario: Report Versus Dashboard<\/h2>\r\n

      The college just started a Campus Travel program run by interns majoring in travel and event management. The program started in January 2024 and ran through April 25, 2024. All interns worked approximately the same number of hours per week but on different days. Occasionally, there was some overlap with other interns. Campus Travel was open 4 hours a day, every day of the week except for normal campus holidays or breaks. Scheduling the interns to work around their classes was particularly difficult. At the end of the semester, the interns worked collaboratively to create a report for their advisor.<\/p>\r\n

      The data had the following categories: (1) date tickets were sold, (2) salesperson (intern), (3) travel destination, and (4) number of tickets sold. The dataset consisted of 35 ticket sale transactions. For the report, the interns used the following calculations: (a) total tickets sold (81), (b) most tickets sold by intern (6), (c) least tickets sold by intern (1), and (d) average number of tickets sold (2.31). You see these calculations in D37:D40.<\/p>\r\n

      The interns used formulas in Excel (sum, max, min, and average) as part of the report. However, the static report was not what the advisor expected. The advisor wanted visuals, and he wanted to be able to interact with the data. The interns were stuck in a basic report mode<\/strong>, assuming that a few calculations and an ability to sort and count the data manually would work; however, the advisor expected an interactive dashboard<\/strong>!<\/p>\r\n

      The advisor wanted to see the number of tickets for each destination and the number of tickets sold by month and by day both as a group and for each intern. Why? Because he wanted to assess the success of the program and determine what standards should be set for the repeat of the program in 2025. So the advisor might be asking these kinds of questions:<\/p>\r\n\r\n

        \r\n \t
      1. Did all interns sell tickets to each destination? If not, why? Was it just a chance of scheduling, or were interns unfamiliar with the destination process?<\/li>\r\n \t
      2. Is there a day of the week that Campus Travel could be closed? Scheduling is a nightmare for the advisor and interns, and both deserve one day off each week.<\/li>\r\n \t
      3. Are there interns who sell more tickets and\/or travel destinations and could help train new interns?<\/li>\r\n \t
      4. Should interns be required to sell a minimum number of tickets?<\/li>\r\n<\/ol>\r\n

        According to Alexander & Walkenbach (2013), \u201cWhen gathering the measures required for the dashboard\u00a0.\u00a0.\u00a0. write out a sentence to describe the measures needed\u201d and determine the types of techniques that will allow the user to interact with the data (p. 15).<\/p>\r\n

        There was a gap in communication. Let\u2019s compare the report and dashboard. Notice the dashboard in Figure 4.1 is driven by a \u201cslicer.\u201d A slicer allows the user to manipulate all the graphs. The slicer provides the interaction and allows the advisor to look at each intern\u2019s progress by selecting their name on the slicer.<\/p>\r\nFigure 4.1\u2014Report Versus Dashboard for Campus Travel.<\/strong>\r\n\r\n[caption id=\"\" align=\"alignnone\" width=\"900\"]\"comparing Note: Author Illustrations (2025)[\/caption]\r\n

        Campus Travel also conducts a posttrip survey that asks three questions. Although the information is easy to read in the table, what technique would make it easier to see insights and trends?<\/p>\r\n\r\n

        What Is a Data Model?<\/h2>\r\n

        Let\u2019s work through the step-by-step process of creating a Microsoft Excel dashboard and the use of a data model. A data model<\/strong> allows you to integrate data from multiple tables and multiple sources! This is a good skill to know since most situations require you to pull data in from different sources. For example, in Campus Travel, the interns keep track of their schedules and about how long it takes to sell a destination package. The advisor might want to consider this use of time\u2014especially if the intern doesn\u2019t actually make the sale but has spent hours of time working with the potential customer.<\/p>\r\n\r\n

        <\/span>Step-by-Step to Create a Simple Dashboard in Microsoft Excel<\/h2>\r\nNote: Screenshots of Excel interface \u00a9 Microsoft Corporation. Used with attribution for instructional and illustrative purposes<\/em>. Annotations added by the author.<\/em>\r\n

        The first step is to explore an existing dashboard that uses two table ranges. The \u201c4.1 Campus Travel Ticket Sales Student File\u201d is in the Chapter 4 Excel files.<\/p>\r\n\r\n

          \r\n \t
        1. Open Microsoft Excel and open the file \u201cCampus Travel Ticket Sales with dashboards.\u201d<\/li>\r\n \t
        2. Explore<\/strong> the data.\r\nLook<\/strong> at the Raw Data file. Notice that it is a table. You can tell it is a table because it allows you to sort using the drop-down arrows shown in the header row. Notice the total number of tickets sold and the other calculations used at the bottom of the table. The table extends from A1 to D36 with a header row that shows Date Sold, Salesperson, Destination, and Tickets Sold. Do not include the calculations.\r\n\r\nClick<\/strong> inside the table, and you will notice a new tab on the ribbon called Table Design. Select the Table Design<\/strong>. You should also notice that the table name shows as \u201cData.\u201d<\/li>\r\n<\/ol>\r\nClick the Home<\/strong> tab on the ribbon and locate the \u201cFormat as Table\u201d within the Styles group. Look through the data and notice that you see the date sold but not the day of the week.\r\n\r\nSelect the Calendar tab<\/strong>. The calendar tab was created so that the date the tickets were sold could be attached to the day of the week. The day of the week formula (shown in Figure 4.2) is =TEXT(A2,\u201cddd\u201d). If you add an extra \u201cd\u201d into the formula, the entire day will be the result. The month<\/strong> formula is =TEXT(A2, \u201cmmmm\u201d) to show the entire month spelled out with no abbreviation.\r\n\r\n[caption id=\"\" align=\"alignnone\" width=\"275\"]\"showing Figure 4.2\u2014Day of the Week Formula<\/strong>[\/caption]\r\n\r\nNext, select the dashboard tab.<\/strong> This is a completed dashboard for you to explore. If you click on any of the chart areas, you will see the PivotChart Fields pop-up. Try interacting with the chart by selecting the name Andrew in the slicer. You should see all three charts change.\r\n\r\nIn addition, click on one of the PivotTables and notice the PivotChart Fields that pop up. It shows both Calendar and Data tables. Note that the Calendar table has \u201cDate Sold\u201d and the Data table has \u201cDate Sold.\u201d There must be a common point between the two tables you want to use. In this case, \u201cDate Sold\u201d is that connection (see Figure 4.3).\r\n\r\n[caption id=\"\" align=\"alignnone\" width=\"341\"]\"shows Figure 4.3\u2014Both Tables Show in the PivotChart Field<\/strong>[\/caption]\r\n\r\nA data model<\/strong> allows the user to integrate multiple tables.\r\n\r\nNext, we will take you through the steps to build the data model.\r\n