In this chapter Jim creates the main report in Power BI Desktop, using the data model he set up in Chapter 3. He then creates various data visualizations needed for his report and uses DAX formulas to get the values he needs. This report will provide a basis for the detailed reports Jim will create in the next chapter.
Getting Set Up
Now that Jim has prepared the layout of his model, he is ready to start designing and creating a main report. Later he will create more reports that provide detailed information around particular subject areas. He will also build a dashboard to summarize the most important information.
Jim knows he needs to collect some basic information to display on the report, and in order to determine what to collect, he must answer questions like these:
- What is the last date for which we have data that is used in the report? How fresh is the data?
- What are the current year and month we are reporting on?
The Power BI Desktop file right now has some pages with data used to validate the measures he created earlier. Jim adds a new page and renames it Overview. He then deletes the other pages because he doesn’t need them anymore.
Jim wants to show the last invoice date, which will help him display the last date for which there is data in the report. He already added the [Absolute Last Invoice Date] measure (see Chapter 3), which will help him with this.
To display the measure in his reports, Jim wants to make sure the reports show the values in the correct format, so he sets the format of the measure to M/D/YY by selecting Format on the Modeling tab and then selecting the format shown in Figure 4.1.
Figure 4.1: Setting the right format.
|
When you set the formatting in a measure, the field stores that formatting. Thereafter, every time you use that measure, the same formatting will be applied. You can, however, overrule this automatically applied formatting on the visual. |
Now Jim goes back to the overview page to verify that it works as expected. He clicks the matrix visual in the Visualizations pane. In the Rows area, he adds FyYear and FyMonth, and in the Values area, he adds Absolute Last Invoice Date. He then adds CurrentFyMonth under Visual Level Filters and selects the value 1 to filter this visual to show just the current fiscal year’s month. Year and Month are nested values in the matrix, but Jim wants to see all the values, so he selects the button that expands everything down one level in the hierarchy on the visual. Now Jim can see the values he can use in his report.
Figure 4.2: Getting the latest invoice date.
But Jim notices that something is not right yet: Reports in his company usually have a clear distinction between fiscal and calendar dates. He therefore decides to add to the query two columns that add a fiscal year label to his model. He selects Edit Queries on the Home tab to make these changes. This opens the Power Query Editor, where he selects the Calendar table and inserts the columns next to the FyMonth column by right-clicking the column and selecting Custom Column.
Figure 4.3: Inserting a custom column between two columns in the Power Query Editor.
|
To make any changes in the Power Query Editor, you need to have access to the data source, so I have already added these columns to the sample file. |
Jim uses three custom column formulas to get the correct labels. This is the first one:
[FyYearLabel]=
"FY" & Text.End(Number.ToText([FyYear]),2)
|
This formula adds the text FY before the last two characters of the FyYear column. |
Figure 4.4: Entering the custom column formula.
This is the second formula:
[FyMonthLabel]=
"M" & Text.PadStart(Text.From([FyMonth]),2,"0")
|
This formula adds the text M before the FyMonth column and adds a trailing 0 when the month is only one character. |
This is the third formula:
[FyQuarterLabel]=
"Q"&Text.From([FyQuarter])
|
This formula adds the text Q before the value in the FyQuarter column. |
When he is done adding the new columns, Jim clicks Close & Apply on the Home tab of the Power Query Editor, and the new columns are added to the model. Jim can now swap out the FyYear and FyMonth columns for the new FyYearLabel and FyMonthLabel columns, and he sees the updated terminology used in the matrix.
Figure 4.5: Fiscal year labels added to the matrix.
Creating the Report
Jim has prepared his model to the point where he can now start creating the actual report, but before he does so, he needs to plan it.
The goal of Jim’s dashboard is to help the management team get quick insights from all data—from HR to sales to the pipeline. Jim needs to do a deep dive into the sales data, which will later be recapped into a dashboard where users can choose a few important metrics (see Chapter 6).
To make sure he shows the right sales-related information, Jim creates a list of all requests the board made and then prioritizes the list as follows:
1. What is the rhythm of the business? How are our key metrics doing?
2. Did the revenue pick up for all regions?
3. Did the new PR effort have an effect in the new markets?
4. What are our top products? Are they improving?
5. Did the cost reduction effort pan out as expected?
Jim has made some decisions based on his interviews with management. One of the most important lessons he has learned over time is to iterate often with the customer—who in this case is the CFO. He often sends out quick emails to his team and the CFO for feedback to make sure his decisions work for her.
Power BI Tip: Doing Short Iterations and Getting Feedback Often
As the creation of a dashboard or report is progressing, make sure you get feedback from end users. Show them what you have created and ask them for their input. In the end, they are the ones who will use the dashboard, and it’s your job to make sure the information is conveyed appropriately. It’s better to get negative feedback early than later on, after you’ve done a lot of work. Also, by including the users in the design process, you make them part of the design, and they will embrace the end result even more if they contributed to it.
Jim removes all the visuals from the page so he can start building the real report. With any report or dashboard, it is very important to make sure you keep it clean and organized. To help with this, Jim turns on Show Gridlines and Snap Objects to Grid in the View tab of Power BI Desktop.
Figure 4.6: Turning on Show Gridlines and Snap Objects to Grid.
Visualization Tip: Designing a Clean Report
Good alignment of the major objects on a report or dashboard—such as tables and charts—makes a big difference. The human brain gets distracted when objects aren’t aligned as it subconsciously tries to align those objects instead of spending its cycles actually understanding the objects. In the 1920s, German psychologists including Kant and Goethe created what they called gestalt theories. Gestalt is German for “essence or shape of an entity’s complete form.” When applied to visual perception, these theories recognize that in order to make sense of information, the human brain tries to organize the information in a particular way. When we humans look at a combination of visuals, we see the whole before we see the individual parts that make up that whole.
Early 20th-century psychologists determined that the fundamental principle of gestalt perception is the law of grouping. This law says that we tend to order our experience in a manner that is regular, orderly, symmetric, and simple. Those psychologists determined a number of principles that, in theory, would allow us to predict how a total visual is interpreted, including proximity, similarity, closure, symmetry, common fate, continuity, good gestalt, and experience. Following these principles helps create a harmonious design that looks and feels right to the user. In recent years, these principles have become more and more important in the design world; many great designers use them intuitively.
What does gestalt have to do with you? And how does it relate to the work you do in Power BI? Well, a complete study of this subject would fill a book on its own, but here we can look at two important ideas that everyone should be able to apply:
- Keep related data close: If you have a matrix with revenue by region on your dashboard and you also have a chart that contains regions and revenue that either tells a different story or that supports the information on the matrix, make sure this information is grouped together. Such grouping reduces friction for the user: Seeing the same or similar information in close proximity makes it easier to understand. You may need to restructure a report or dashboard after you add additional pieces, and that can be a chore. But I assure you it’s well worth the effort.
- Check your alignment: Make sure all tables and charts are aligned and, if possible, of equal width and height. This will give your dashboard a much cleaner look and make it easier for the user to “grok” the information. Here a few simple tips for getting good alignment: First of all, when possible, align all your charts and tables to the same gridlines when placed above or next to each other. If you set Snap Objects to Grid, it will be easier to align them on the page with other charts and tables. When everything is set to align to the page grid, it’s a piece of cake to make objects the same width. Sometimes you need to get creative with charts to make them fit, but again, doing so is well worth it. Also, don’t be afraid to leave some empty space between charts or tables; it’s better to have some empty space than to cram together your visuals.
If you follow these two simple guidelines, your reports will be much easier to understand and read.
The following two figures show how you can quickly and easily get great results from some simple alignment.
Figure 4.7: A busy report that’s not aligned.
Figure 4.8: The same report, now neat and organized.
One of the main proponents of clean, well-designed reports and dashboards is Stephen Few. His books Information Dashboard Design (http://ppivot.us/fds543s) and Show Me the Numbers (http://ppivot.us/fw43s4s) cover this in great detail and are must-reads for every business analyst.
Next, Jim adds a title to the dashboard—Strategic Targets Overview—and two important labels to show when the data was refreshed last (Data As Of:) and identify the current reporting period (Reporting Period:). For this he uses three separate text boxes, and he chooses a light color for the text.
Figure 4.9: Adding the main title by using a text box.
|
The color for the title in Figure 4.9 is #8497b0. It can be set by using custom colors under the text color option, which is highlighted in Figure 4.9. |
Visualization Tip: Choosing the Right Colors for a Dashboard
It is important to spend time thinking about the colors you want to use in a report or dashboard. Colors can be grouped into two types: aggressive and passive. Aggressive colors (such as red and green) attract attention immediately and imply importance, while passive colors (such as light gray) are soothing to the eye.
You don’t want to attract attention to anything in your dashboard that doesn’t warrant it. For example, using big bright labels with all uppercase and bold font will attract the eyes away from the actual data presented.
Figure 4.10: The brighter, all-uppercase label attracts the eye immediately.
Contextual data such as labels should never compete with the actual data on a report. Such labels provide additional information that a user needs only when learning what data the report displays.
If you use color to attract the user’s attention, make sure to use it sparingly. Otherwise, the user will get used to it, and it will no longer be a differentiator.
It’s a good idea to define a set of colors that you want to use for a dashboard up front and try to add new colors to the set only when needed. A good way to get consistent colors is to create a Power BI theme and apply it to all your reports (see http://ppivot.us/th34sd3).
Jim wants to create a workbook that looks crisp and is easily readable, so he plans to use just one font for his dashboard: Segoe UI. In this case, the text Jim adds is a header, and he increases the font size to 24; for the other text boxes, he chooses a smaller size, 14.
Visualization Tip: Using Fonts
The same principles you need to consider with colors apply to fonts as well. For example, using too many fonts will distract a user from the information that you want to display. Before you start working on a dashboard or report, decide what fonts to use and stick to a maximum of two of them to reduce distraction.
While working on this book, I stumbled across research from MIT which found that a badly chosen font for a car dashboard can distract you from the road and increase your chance of crashing (see http://ppivot.us/SSFPC). This is a good lesson in how important typography can be.
Another factor that might be important is that a dashboard may need to use a business’s standard typography and colors. Does the company have a default font? Maybe even colors? The CFO and CIO usually spend a lot of planning time and money on the website or corporate style and typically like it if they see these settings used in company reports as well. It’s usually easy to find a company’s colors, typography, and styling by looking at its website.
To clearly separate the title from the report, Jim uses a line shape to add a thick line under the title bar and adds the logo of the company to the right.
Figure 4.11: Creating the header.
|
A change from the default that I have made for this book is to set the page size to 4:3 instead of the default 16:9. This allowed me to make better screenshots to fit the book format. Changing this setting can be done in the Page Size properties on the Format pane. You can change from the Fields pane to the Format pane by selecting the little paint roller that is shown in Figure 4.12. For more on the Format pane, see http://ppivot.us/pspbi23. |
Jim uses the [Absolute Last Invoice Date] measure from the Calendar table to display the last date the data was refreshed. He adds it to the report as a card. To format it, he opens the Format tab and sets the font to Segoe UI with size 14 and then deselects Category Label.
Figure 4.12: Changing the format of the visual.
When this is finalized, Jim positions the card visual next to the text box. He temporarily turns off Snap Objects to Grid and uses the Ctrl key and arrow keys on his keyboard to do the fine maneuvering of the visual.
|
You will see how to add the Reporting Period: label later in this chapter. |
Jim is now ready to add the first piece of data to the report. In one of the interview sessions, he learned that revenue isn’t equal for all regions. It is very important for the management team to keep an eye on the revenue by region. Therefore, Jim wants to add revenue, the revenue variance-to-target, the year-over-year change for the current month, and the variance-to-target trend over time by region for the current month.
The company uses several acronyms in its reports, including $VTT for variance-to-target in dollars and YoY% for year-over-year change in percentage. Jim will use these in the dashboards and reports.
Jim drags a matrix onto the overview page to visualize the data by region.
Figure 4.13: Inserting a matrix.
Visualization Tip: Keeping Space Around the Edges
Using space well when visualizing data is an important art. Without the appropriate amount of space, a report looks too dense and busy. Make sure you keep the appropriate space between visuals and the edge of the report.
Jim now adds the Region field from the PoliticalGeography table to Rows on the matrix, and he adds RevenueAmount and RevenueTarget from the Invoice table under Values. The columns that are dragged into the Values area are automatically aggregated for each region; in this case, they are aggregated using SUM.
One of the primary values that Jim wants to display in the dashboard and reports is the sum of revenue. Instead of using the built-in SUM function, he creates a measure that he will be able to use as a base for other measures. He creates the new measure by right-clicking the Invoice table in the Fields pane and selecting New Measure. The formula bar opens, and Jim can write his new measure.
Figure 4.14: Creating a new measure to replace the default fields.
Jim creates the following measure:
[Sum of Revenue] =
He also creates a measure for revenue target:
[Sum of RevenueTarget] =
SUM([RevenueTarget])
For both of these measures, Jim uses the Modeling tab to set the format to currency and the number of decimal places to two.
Figure 4.15: Formatting the measure.
Now he replaces the columns from the Values area with the new measures.
Data Model Tip: Creating and Managing Measures
As a model gets more complex and you add more business logic to it, it becomes more and more important that you manage your measures. Here are a few tips that will help you manage large models that contain complex measures:
- Split a complex measure into several separate measures. When you do this, you can create and debug parts of the calculation separately. You might also be able to reuse measures, which makes mistakes less likely.
- Choose similar names for calculations over the same base field (for example, [Sum of Revenue], [Count of Revenue], [Revenue YoY%], and [Revenue to Target]). You can always change the name that appears on the report.
- Name a measure as clearly as possible to describe what it does.
- Hide measures that are not directly used or useful in reports (for example, intermediate measures). Hidden measures can still be referenced by DAX calculations.
- Format measures appropriately to save work later and ensure that the numbers are perceived correctly.
Because the reports need to show the current month, Jim uses the calculated column he created earlier to set the report filter to CurrentFyMonth. He turns off the Grand Total row by selecting the matrix and then setting Row Totals to Off under Subtotals in the Format pane. The table now looks as shown in Figure 4.16.
Figure 4.16: Adding fields to the matrix.
Instead of showing the revenue and its target directly, Jim wants to show the variance-to-target, so he adds a new measure with the following expression:
[Revenue to target]=
[Sum of Revenue] - [Sum of RevenueTarget]
This expression calculates the variance-to-target for each region when put in the matrix, with Region under Rows.
Figure 4.17: Also adding the [Revenue to target] measure to the matrix.
Now Jim removes the Sum of RevenueTarget column from the matrix.
Next, Jim wants to add some styling. He decides to apply 9-point Segoe UI font to the matrix, so he selects the matrix and on the Format pane he changes the font size for both the column and row headers and the Values area. Next, he wants to change the style of the matrix to a clean and tidy look, so he selects Minimal under the Style property on the Format Pane.
Figure 4.18: Formatting applied to the matrix.
One of the most important metrics for Contoso is year-over-year change. Jim’s model is already set up correctly to follow the golden rules of time intelligence (see Chapter 3), so he is ready to create a measure that will compare the values of the current month to the values of the same month last year.
Jim knows it’s a good idea to separate complex measures into multiple calculations. The year-over-year growth formula is quite simple:
(Current revenue - Previous revenue) / Previous revenue = Percentage growth
Jim already has current revenue in his [Sum of Revenue] measure, but he needs to add a [Sum of Revenue PreviousYear] measure. Therefore, Jim creates the following measure:
[Sum of Revenue PreviousYear] =
IF(SELECTEDVALUE(Calendar[FyYear]),
CALCULATE([Sum of Revenue]
,DATEADD(Calendar[Date]
,-1
,YEAR
)
)
)
A blank value, an empty cell, and a missing value are all represented by a special value type: BLANK. Why is this important? One of the main characteristics of working with data in Power BI is that, by default, blank values are never shown in visuals. By using the DAX BLANK() function to return a BLANK value, you can determine whether you want Power BI to show something for the row or column for which the calculation is evaluated. This behavior is similar to behavior in Excel. (A blank value is treated a little differently in DAX than in Excel, however. For more details, see http://ppivot.us/JYCSL.)
Next, Jim makes sure the format is set to currency with two decimal places.
Data Model Tip: Understanding How Time Intelligence Functions Work
What does it mean when I say that the calculation above will calculate the sum of revenue shifted backward in time by one year from the dates in the current context?
When you use time intelligence functions such as DATEADD and SAMEPERIODLASTYEAR, DAX tries to determine the dates for the currently selected period in the current cell. The following example shows the sum of revenue by fiscal year (in the boxes) and by fiscal month (underlined).
Figure 4.19: Showing the context.
DAX tries to use the dates in the Calendar table that are set up using the time intelligence golden rules to determine what period is selected in the range for the current cell. It recognizes the boxed values to be by year and the underlined ones to be by month. DAX is able to recognize years, quarters, months, and days. It uses the start and end dates for the entire period in the Calendar table to traverse backward or forward in time; it does not use the actual dates that you have as values. In this example, notice that FY18 has only five months’ worth of data, but this does not mean that DAX will use only those five months when getting the data for the previous year; it will use the entire year because for the year selection, the entire period is selected. For FY18 in this table, the last seven months don’t have data yet, so they are not shown on a month level.
Figure 4.20 shows what happens when you add the previous year formula to the matrix.
Figure 4.20: Shifting dates.
Now you can clearly see that for each cell, DAX uses the date range of the current cell to traverse time. This is one of the important reasons for including in your model a separate date table that contains a continuous date range.
For more information on this and similar concepts, see http://ppivot.us/KQSEF.
Jim wants to hide the measure he just created because he doesn’t want this field to clutter the Fields pane. He does so by right-clicking the field and selecting Hide.
Figure 4.21: Hiding the measure.
Variables are a new construct in DAX, introduced at the same time as Power BI. Variables allow you to split up a measure into several calculations within the formula itself. Using variables has two major benefits:
- It makes a complicated measure much more readable.
- It improves performance. The results of the expressions are stored as named variables. This means that if you want to use the same expression multiple times in your formula, it gets executed only once instead of multiple times, as would be the case if you referenced the expression directly.
You use the VAR statement to assign an expression to a named variable. For a simple scenario, it might look like this:
[Calculation]=
//Define the Sales variable
VAR Sales = [Sum of SalesAmount]
//Define the SalesAllTime variable
VAR SalesAllTime = CALCULATE([Sum of SalesAmount]
,ALL(Calendar)
//Return the outcome of the measure
RETURN IF(NOT(ISBLANK(Sales))
,DIVIDE(
Sales
,SalesAllTime
)
)
You might wonder why the Sales variable isn’t used in the SalesAllTime variable definition. This is because a filter context change cannot be applied to the outcome of a variable. In the SalesAllTime variable the outcome of the sum of salesamount value isn’t used, we ask the engine to calculate sum of salesamount for a different filter content.
As you can see, this example uses the Sales variable twice, but the data model will have to calculate the results only once. This is a very simple expression, so the performance difference might be negligible, but in complicated expressions, using VAR can be very efficient.
In Power BI, it is possible to add comments to your formulas. If you start a line with //, the language parser will ignore the rest of the line, and you can type anything you want to annotate your expressions. You can also use /* to start a comment and then */ to end it. This is the method to use when you want a comment to span multiple lines.
Comments can be very useful in complicated scenarios or when you work on a project with multiple people as they can help keep track of the meaning or purpose for each calculation.
Next, Jim creates the year-over-year calculation:
[Sum of Revenue YoY%] =
VAR Revenue = [Sum of Revenue]
VAR Revenueprevyear = [Sum of Revenue PreviousYear]
RETURN IF(
NOT(ISBLANK(Revenue)),
DIVIDE(
(Revenue
- Revenueprevyear)
,Revenueprevyear
)
)
|
This calculation determines the year-over-year change when [Sum of Revenue] is not empty for the current cell; if it is empty, the formula returns BLANK. You would want to add such a test for empty values with time periods in the future when you have values for the previous year but not for the current time period; when this is the case, Power BI returning BLANK will show a skewed result. So when there is revenue for the current time period, the DIVIDE function divides the result of the subtraction of [Sum of Revenue PreviousYear] from [Sum of Revenue] by [Sum of Revenue PreviousYear]. |
Data Model Tip: The DIVIDE Function
The DIVIDE function ensures that either BLANK or an actual value is returned and is optimized for performance. When you use the / operator, DAX has to handle the divide-by-zero error every time it happens, and this results in a performance hit. The DIVIDE function is short-circuited to just return BLANK or an error value that you can optionally enter in the arguments when a divide-by-zero error is encountered. It’s recommended to use the DIVIDE function whenever possible.
Because this is a percentage, Jim changes the format to percentage, and he adds the measure to the matrix.
Figure 4.22: The measure is added to a matrix.
Jim has created all the calculations needed for the regions view. He doesn’t like the titles of the headers, so he changes them in the matrix. To do so, he double-clicks the name of a column in the Values area and types a new label. Measures usually have very long names, and using them as is results in a lot of unused whitespace in a visual. Renaming the labels to make them shorter allows Jim to get rid of this whitespace and make the visual more understandable for the end users.
Figure 4.23: Renaming column labels.
Next, Jim sets the alignment of the column headers to center alignment in the Format pane.
Jim usually customizes the formatting at the visual level, but in this case he just wants to change the default decimal places to zero because on such large numbers, extra decimal places don’t add much value but take up a lot of space and make the numbers harder to read. On the Format pane, in the Field Formatting section, for each field he sets Value Decimal Places to 0.
Figure 4.24: The appropriate formatting is applied.
Visualization Tip: Choosing the Right Precision
Here’s another opportunity to make your information easier to digest: Determine what level of precision you need for the values you show. Dashboards usually present information at a very high level. Does it really make a difference if you show two decimal places? Or can you get rid of them? $45,223.12 and $45,223 tell the same story when you’re talking about the revenue of an entire company. Of course, you should use your best judgment and not remove too much information. For example, in service-level agreements (SLAs), the difference between 99.34% and 99.96% is likely to be crucial, so you should keep the decimal places.
Changing the precision is easy to do, and you’ll be surprised how much improvement this simple step will gain you.
One of the things Jim has learned over the years is that it is important to make sure a matrix will not change layout whenever changes are made to the sheet, which would ruin the layout he has so painstakingly crafted. He therefore selects the matrix and turns off Auto-Size Column Width under Column Headers in the Format pane.
Figure 4.25: Disabling Auto-Size Column Width for columns in a matrix prevents hassle later on.
In Jim’s interviews with the board members, he learned that in addition to the information for each region for the current month, they also want to be able to see the trend. Jim therefore wants to show, for each region, the trend for the past 12 months. He wants to do this in a small and inconspicuous way while still managing to convey the trend. He decides to create a sparkline that shows revenue for each region for the past 12 months. He wants to put the sparkline next to the matrix he created so that both of them show data for the same regions.
Power BI Tip: Using Custom Visuals
Power BI contains many visualizations out of the box, but Power BI reports and dashboards can be extended with custom visualizations built by the community or by specialized companies. Power BI has a store where you can download many of these visuals that range from word clouds to Gantt charts and Mekko charts. You can find details on custom visuals at http://ppivot.us/chixf32f.
To add a sparkline in Power BI Desktop, Jim needs to add the sparkline visual to his list of visuals. To do this, he clicks the three dots in the Visualizations pane and selects Import from Store, which opens the custom visuals store.
Figure 4.26: Browsing the custom visuals store.
Jim finds the sparkline visual and clicks Add next to it. The custom visual is added to his list of available visualizations.
Figure 4.27: The custom visual is added to the Visualizations pane.
Jim now drags the sparkline visualization onto the canvas, and because he wants to see the sales over the past 12 months, he drags YearMonth to the Axis area and Region to the Category area.
To make sure just data from the past 12 months appears, counting back from the last month for which he has data, Jim can use the previously created calculated column Running 12 FY Months, which returns 1 for each day in the past 12 months that has data. He adds this column under Visual Level Filters to automatically select the past 12 months for which he has data.
Next, Jim adds the measure [Revenue to Target] to the matrix to get values for revenue variance-to-target for the past 12 months for which there is data. He now has the sparkline he wants.
Figure 4.28: Revenue variance-to-target for the past 12 months.
Visualization Tip: Understanding Sparklines
Professor Edward Tufte introduced the sparkline in his book Beautiful Evidence (http://ppivot.us/fgsd3fd). He said, “A sparkline is a small, intense, simple, word-sized graphic with typographic resolution. Sparklines mean that graphics are no longer cartoonish special occasions with captions and boxes, but rather sparkline graphics can be everywhere a word or number can be: embedded in a sentence, table, headline, map, spreadsheet, graphic.”
Tufte invented the sparkline as a graphic that shows a trend in a space where you would usually have a label or text. A simple graphic shows trends much more clearly than does a range of numbers. Sparklines also save space on reports.
There are three main types of sparklines:
- Line: A line sparkline is usually used for chronological time series and other sequence values on the axis, such as stages of a production process. Line sparklines are very useful in helping spot changes or trends in data over time. This is the type Jim is using in Power BI.
- Column: A column sparkline is usually used to compare amounts between specific discrete categorical values.
- Win/loss: A win/loss sparkline is a special form of sparkline that shows whether data is positive (such as a win) or negative (such as a loss). This type of sparkline is great for showing stock tickers.
For more information, see the extensive Q&A on sparklines on Edward Tufte’s website: http://ppivot.us/ZBPHS. For more examples of sparklines in Excel, see this great article full of examples from Bill Jelen: http://ppivot.us/GSEVU.
Jim moves the matrix to the right to create space for the sparkline and then moves the sparkline to the left of the matrix. To save space, Jim turns off the category labels in the Format pane because the same labels are also present in the matrix.
Figure 4.29: Sparklines moved into position.
Jim uses a text box to add the title $VTT 12 Months to the column that contains the sparklines. This great visual allows Jim to show the trend for each region over the past 12 months.
Figure 4.30: Sparklines applied to the report.
From the dashboard that Jim has created, it’s pretty clear that the targets have been met for all regions. Interestingly, the west region was the weakest growth area. Maybe the target was not aggressive enough. The information provided by the dashboard will definitely provide enough material to generate some discussion in the next management meeting.
The next report Jim wants to tackle is one to help gain insight into whether the marketing efforts in new markets have resulted in increased revenue in those markets. Each region was responsible for its own marketing, and the board will want to see this information split out by region.
Jim decides to use a graph, split out by region, to present the number of markets for which there have been sales over the past 12 months.
Visualization Tip: Choosing the Right Chart
Choosing the right chart to show data might be the most difficult problem in data visualization. Unfortunately, there is no surefire way to choose the right chart. Which one is best depends on many, many factors—and on the data you want to show. You have to think about what information you want to show and what information is important. The core idea behind graphs and charts is that they help people understand data quickly and allow you to tell the story behind the data. Therefore, an important factor in choosing and designing the right chart is having a good understanding of the data and the types of charts.
There are four basic types of chart visualizations: those that make a comparison between data points, those that show the distribution of data points, those that show the relationships between data points, and those that show how data points are put together (composition). These visualizations help your audience see what you are talking about.
Let’s look at an example for each type:
- Comparison: You use this type of chart when you want to compare two or more data points, such as the revenue for each month between years, revenue by region, or revenue for each month for the current year. This is the most common type and is usually a line, bar, or column chart.
- Distribution: This is the second most common chart category. As the name suggests, a distribution chart is used to display how data is distributed and to understand outliers and categories that are outside the norm (for example, distribution of voters per region, types of returned products over the past month).
- Relationship: This type of chart shows interesting relationships that can lead to new understanding about correlations and causality between a wide range of variables. For example, you might use this type of chart if you want to prove whether hours of study make for better results or to show the relationship between in-store sales and holidays. The most common relationship charts are scatter plots and bubble charts.
- Composition: This type of chart allows you to display how specific data compares to broader data (for example, what browser types are visiting a website, product sales as a percentage of total revenue). Commonly used composition charts are column charts, bar charts, and pie charts.
When I’m trying to choose the right chart, I often use this graphic created by Andrew Abela: http://ppivot.us/TPXGX.
Jim adds a line chart to the report, directly besides the regions matrix he just created. Next, he adds FyYearLabel and FyMonthLabel under Axis, Region under Legend, and Running 12 FY Months under Filters (to return only the past 12 months).
Jim then adds a measure for counting the number of distinct cities that have revenue. In the Invoice table, every Contoso Communications sales transaction creates a new row. Every row in the Transaction table contains a reference to where the transaction happened, and this information is stored in the PoliticalGeographyKey column. Here is Jim’s measure for distinctly counting the markets:
[Nr of markets] =
DISTINCTCOUNT(Invoice[PoliticalGeographyKey])
|
The DISTINCTCOUNT function counts the distinct number of occurrences for each value of PoliticalGeographyKey column in the Invoice table. |
Data Model Tip: Remembering That Context Is Always Applied
As mention in Chapter 3, it’s important to remember the context in which a calculation will be executed. The expression above will always calculate DISTINCTCOUNT for Invoice[PoliticalGeographyKey] for the values in that cell, filtered by what is on Rows, Columns, Filters, and Slicers. Whether you put regions, years, or products on Rows, the value of DISTINCTCOUNT for Invoice[PoliticalGeographyKey] will automatically be calculated for the values that determine the context.
Adding this measure to the line chart gives Jim almost the chart he wants. However, right now the chart is sorted in descending order, which means the last year appears first. To fix this, he clicks on the three dots in the upper-right corner and selects the ascending sort order. Figure 4.31 shows the resulting chart.
Figure 4.31: Adding values to the chart.
Jim is not satisfied yet because he wants to see the months and years in the chart. To fix this, he clicks the expand icon on the chart’s top bar and then expands the values of the chart over both values of the axis by drilling down. This still doesn’t give him what he wants because the values are repeated for each month. To change this, he turns off the Concatenate Labels option for the x-axis in the Format pane.
Figure 4.32: Changing the chart’s formatting options.
Now Jim wants to align this chart perfectly with the matrix, so he ensures that the chart automatically snaps to the gridlines when it’s placed on the grid. Next, he turns off the title. Finally, he makes the chart a little wider on the x-axis and smaller on the y-axis. Jim now has a clear chart of the market growth over the past 12 months.
Figure 4.33: The finished chart.
Jim looks for feedback on the first iteration of the dashboard from several business users who will be using the report. The feedback he gets is overwhelmingly positive, but one remark he hears multiple times is that the users wish they could see the report show either the current month, quarter-to-date (QTD), or year-to-date (YTD). Jim agrees that this would be a great addition to the report, and he knows that slicers can help him achieve it. Users will be able to select whatever time period they want to see.
To create slicers, he needs to have data to show, so Jim clicks Enter Data on the Home tab and enters the values he wants: Actual, QTD, and YTD. He names the table varPeriod and the column Period.
Figure 4.34: Adding new values to a new table.
After Jim clicks Load, these values are added to a new table in the model.
Jim can now use this data to create a slicer. He selects the visuals on the screen and drags them down. Next, he inserts a slicer visual and adds the Period column he just created.
Figure 4.35: Adding values to a slicer.
Jim changes the orientation to Horizontal in the Format pane of the slicer under General. This makes the slicer values sit next to each other instead of one above the other. Next, he turns off the header and adds an outline for the items of the type frame. He matches the color of the outline with the color used as the matrix line. Finally, he arranges the slicer above the matrix.
Figure 4.36: The slicer is added to the report.
To make sure the users of the report cannot select multiple values at the same time, Jim turns on Single Select under the selection controls on the Format pane.
Based on the slicer selection, Jim wants to show the actual, fiscal quarter-to-date, or fiscal year-to-date value for each calculation. Jim already has the values for actual, and he decides to add separate measures for fiscal QTD and fiscal YTD.
He adds a measure that determines the fiscal year-to-date sum of revenue:
[Sum of Revenue F YTD]=
IF(HASONEVALUE(Calendar[FyYear]),
TOTALYTD([Sum of Revenue]
,Calendar[Date]
,"06/30")
)
Next, Jim creates a measure that uses Sum of Revenue F YTD to determine the fiscal year-to-date sum for the previous year:
[Sum of Revenue F YTD PreviousYear]=
IF(HASONEVALUE(Calendar[FyYear]),
CALCULATE([Sum of Revenue F YTD]
,DATEADD(Calendar[Date]
, -1
, YEAR)
)
)
|
This calculation finds the Sum of Revenue F YTD for the entire YTD. But instead of using the date of the cell in the visual, DATEADD moves this date back one year from the dates in the current cell selection. |
Jim then creates a measure that determines the year-over-year growth by subtracting the revenue for the current fiscal YTD from the revenue for the previous fiscal YTD and divides the result by the revenue for the previous fiscal YTD—but only when there is revenue for the current year:
[Sum of Revenue F YTD YoY%]=
IF(NOT(ISBLANK([Sum of Revenue])),
DIVIDE([Sum of Revenue F YTD]
- [Sum of Revenue F YTD PreviousYear]
,[Sum of Revenue F YTD PreviousYear])
)
|
The DIVIDE function is used to make sure divide-by-zero errors will not be thrown. Instead, when these errors occur, blank values will be returned. |
Jim now uses the same technique to calculate the fiscal year-to-date sum of revenue target:
[Sum of RevenueTarget F YTD]=
IF(HASONEVALUE('Calendar'[FyYear]),
TOTALYTD([Sum of RevenueTarget],
'Calendar'[Date]
,"06/30")
)
Finally, he creates a measure to calculate the revenue variance-to-target for the fiscal year by subtracting [Sum of RevenueTarget F YTD] from [Sum of Revenue F YTD]:
[Revenue to target F YTD]=
[Sum of Revenue F YTD]-[Sum of RevenueTarget F YTD]
Jim creates the same calculation for fiscal quarter-to-date:
[Sum of Revenue F QTD] =
IF(HASONEVALUE(Calendar[FYQuarter]),
TOTALQTD([Sum of Revenue]
,Calendar[Date])
)
|
Because fiscal quarters usually don’t run differently from normal quarters, this calculation can use the TOTALQTD function. |
To calculate Revenue to Target F QTD and Sum of Revenue F QTD YoY%, Jim uses the same calculation pattern as for the YTD measures but replaces the [Sum of Revenue F YTD] measure for [Sum of Revenue F QTD].
After Jim has added measures to calculate revenue, variance-to-target, and year-over-year growth for the current month, fiscal QTD, and fiscal YTD, he needs to find a way to make the values on his report respect the slicer he just created. Jim therefore creates a measure that he can reference in all other measures to check whether the slicer has only one value selected. He doesn’t want to show any value when the user selects both YTD and QTD. He creates the following measure to check whether the slicer has only one value selected:
[isReportSlicerSet]=
HASONEVALUE(varPeriod[Period])
|
This measure uses the HASONEVALUE function to return true or false, depending on whether varPeriod[Period] has one value for the current cell where the calculation field is executed in the visual. |
Before he continues, Jim hides the measure because he doesn’t need it in anything except as a building block in other measures.
Jim next creates the following measure to return a result based on the value of the slicer:
RevenueByPeriod =
IF([isReportSlicerSet],
SWITCH(VALUES(varPeriod[Period]),
"Actual",[Sum of Revenue],
"YTD",[Sum of Revenue F YTD],
"QTD",[Sum of Revenue F QTD]
)
)
|
When the [isReportSlicerSet] measure returns true, the VALUES function determines the current value for varPeriod[Period] for this visual. If you didn’t check for the single value of varPeriod[Period] and the user selected multiple values in the slicer, the VALUES function would return an error because it wouldn’t return a single value. The SWITCH statement then determines which measure will be executed, based on the value of varPeriod[Period]. |
Data Model Tip: The VALUES Function
The VALUES function allows you to use values in the current context of the visual. Consider the follow DAX formula:
[Test Value]=
IF(HASONEVALUE(PoliticalGeography[Region]) &&
HASONEVALUE(Calendar[FyYearLabel]),
VALUES(PoliticalGeography[Region]) &
" " &
VALUES(Calendar[FyYearLabel]))
This formula shows the value of PoliticalGeography[Region] and Calendar[FyYearLabel] in each cell in the matrix where the cell has only one value for each.
Figure 4.37: The VALUES function is used to show the context.
You can use the VALUES function in many situations, such as in a running sum, where you use it to get the current month to determine the time range for a calculation.
Jim repeats the same pattern for variation-to-target and year-over-year revenue.
|
You can download a file that contains all the measures used until Figure 4.47 from http://ppivot.us/daxm231. You can also download a text file that has all the measures for this part of the chapter from http://ppivot.us/filmea2s. The following measures are small variations of the measures just created so are not really interesting to cover in detail here. |
Next, Jim replaces the measures from the matrix with the newly created measures. He also sets the same formatting and applies the same header names.
Figure 4.38: The new measures are added to the report.
The measures are empty, as expected, so Jim clicks the YTD slicer, and now the measures show the values he expects.
Figure 4.39: Values in the matrix now show up.
Jim also wants to hook up the Reporting Period: title to the slicer to report the right reporting period for each slicer. To make this happen, he adds the following new measure, which checks the selection and generates a title on demand:
Reporting Period =
IF(HASONEVALUE(varPeriod[Period]),
SWITCH(VALUES(varPeriod[Period])
,"Actual",
CALCULATE(
LASTNONBLANK(Calendar[FYMonthLabel],1)
,Calendar[CurrentFyMonth]=1)
& " " &
CALCULATE(
LASTNONBLANK(Calendar[FyYearLabel],1)
,Calendar[CurrentFyMonth]=1)
,"QTD",
CALCULATE(
LASTNONBLANK(Calendar[FyQuarterLabel],1)
,Calendar[CurrentFyMonth]=1)
& " " &
CALCULATE(
LASTNONBLANK(Calendar[FyYearLabel],1)
,Calendar[CurrentFyMonth]=1)
,"YTD",
CALCULATE(
LASTNONBLANK(Calendar[FyYearLabel],1)
,Calendar[CurrentFyMonth]=1)
)
, "N/A")
|
I chose to use to use HASONEVALUE here instead of using SELECTEDVALUE as I want to have the measure return N/A when nothing can be returned. |
Now Jim adds this measure to a card visual, turns off the category label, and changes the text to size 11 and font Segoe UI. Placing the card visual next to the Reporting Period: label gives him what he wants.
Figure 4.40: The title reacts to slicer clicks.
The next part of the dashboard is really the centerpiece of the report. One of the most important requirements is to be able to see the rhythm of the business for each key metric for the current month, current QTD, and current fiscal YTD. The key metrics of the business are revenue, units, usage, and subscribers. For each of these metrics, Jim needs to show the actuals, variance-to-target, and year-over-year growth for each time period in the report. This report needs to be connected to the slicer he just created.
Jim has been creating these reports for years and knows what to do. He knows that the regular matrix is unable to achieve the type of layout he needs, and he has to use a different approach. He wants the visual to show up at the top of the report, so Jim moves all the visuals down. He makes sure the reports are aligned properly and ensures that they have the same style.
Figure 4.41: Creating the framework for the report.
Jim now wants to add the values of the metrics to the report. He decides to create another table called KeyMetrics to define the metrics, and he does so by clicking the Enter Data button (http://ppivot.us/entdata).
Figure 4.42: Adding a new table for the values.
To make sure he can sort the values in the table in any way, Jim includes a position field. The default sorting of any column is alphabetical. After loading the report, Jim sets the sorting of the column to Pos, which causes the values to be sorted by the numbers in the Pos column instead of alphabetically.
Figure 4.43: Changing the sorting of the Values column.
Jim now adds the Values column to the matrix to get the metrics he needs to write another DAX expression. But before he can start on this, he needs to create measures for the Actual, VTT, and YoY% metrics for units, usage, and subscribers, using the same calculation template he created for revenue.
Data Model Tip: Making a Template of Your Measures
You will likely find that you create the same measures over and over, with just small variations, just as Jim did. In his case, Jim used a different base measure, but the rest stayed the same. Keep this in the back of your mind as you design your measures. It is important to split up your measures into smaller calculations and reuse those building blocks wherever possible. Also, make sure to hide the intermediate calculations when you don’t think there is going to be a need for them in your visuals. Hidden calculations will not show in the Fields pane, but you will be able to reference them in other calculations.
To make use of the values in the Values column, Jim creates another measure to determine which measure to show in the matrix:
SWITCH(SELECTEDVALUE(Keymetrics[Values])
,"Revenue"
,FORMAT([RevenueByPeriod], "$#,##0")
,"Subscribers"
,FORMAT([SubscriberCountByPeriod], "#,##")
,"Units"
,FORMAT([UnitsByPeriod], "#,##")
,"Usage"
,FORMAT([UsageByPeriod], "#,##")
)
When Jim adds this measure to the matrix, the values are aligned left. Because Jim used the FORMAT function, the data was returned as text. Using the Field Formatting settings in the Format pane, he changes the alignment to right alignment. To get the sales based on the current period, he adds CurrentFyMonth under Visual Level Filter.
Figure 4.44: Adding the special measure.
Next, Jim changes the measure name to Actual and adds a new measure for the variance-to-target values:
[KeyMetricVTT] =
SWITCH(SELECTEDVALUE(Keymetrics[Values])
,"Revenue"
,FORMAT([RevenueToTargetByPeriod], "$#,##0")
,"Subscribers"
,"n/a"
,"Units"
,FORMAT([UnitsToTargetByPeriod], "#,##")
,"Usage"
,FORMAT([UsageToTargetByPeriod], "#,##")
)
Because the company doesn’t have any targets for subscribers, Jim enters n/a for not available under VTT for Subscribers. He also changes the field formatting to be aligned right.
Finally, Jim creates this measure for YoY%:
[KeyMetricYoY] =
SWITCH(SELECTEDVALUE(Keymetrics[Values])
,"Revenue", [RevenueYoYByPeriod]
,"Subscribers", [SubscriberCountYoYByPeriod]
,"Units", [UnitsYoYByPeriod]
,"Usage",[UsageYoYByPeriod])
Here, because everything is a percentage, Jim doesn’t have to use the Format function, but he uses the default format that is set on the measure.
Putting together these measures gives Jim a matrix that show all values that react to selection in the slicer above it.
Figure 4.45: The matrix with the measures added.
Jim also wants to show the trend for each of the metrics for the past 12 months, so he creates another sparkline. To do this, he creates a similar measure to display the value for each type:
[KeyMetricSparkline] =
SWITCH(SELECTEDVALUE(Keymetrics[Values])
,"Revenue", [Sum of Revenue]
,"Subscribers", [Sum of SubscriberCount]
,"Units", [Sum of Units]
,"Usage", [Sum of Usage])
He then adds the same sparkline visual to the report. Jim also adds Running 12 Months under Filter and the new [KeyMetricSparkline] measure under Values. Finally, he adds YearMonth under Axis Columns and turns off the category label.
Figure 4.46: The sparkline added to the report.
Jim also adds the title Last 12 Months above the matrix, and then, noticing that the two matrixes are not aligned, he spends some time aligning and working on synchronizing their formatting until he gets the desired result. He also makes sure he maximizes the space for the sparklines.
Figure 4.47: After aligning the visuals.
Jim wants to make the revenue over the past 12 months even more pronounced in the report. To accomplish this, he’s going to add a new chart that shows $VTT over the past 12 months. He adds a line chart to the right of the matrix he just created and aligns the two by selecting them both and using the Align tools on the Format pane to align to the top. Then he also makes sure it is aligned to the already existing chart below.
Visualization Tip: Choosing the Right Calculation
Another tip that will help you make your reports more readable is to take a good look at the calculations you use in your charts. Remember that if you can convey the same information with fewer visuals, your report will be easier to read. For example, say that you want to compare values with targets. If you want to visualize this, you will probably create a bar chart with revenue and revenue target by fiscal year.
Figure 4.48: Showing both revenue and revenue target by fiscal year.
This chart allows you to see that the revenue has been outpacing the revenue target for the past couple years. However, you need to take some time to read the labels and look at the information up close. Now, if you show the same information in a different way—subtracting revenue target from revenue in a measure and creating a line chart out of that—it is much easier to see that the revenue has been growing more quickly than the revenue target over the past couple years; you can see this just by looking at the shape of the line.
Figure 4.49: The same information shown as a single line.
Of course, you have to use good judgment and make sure you don’t get rid of too much information. How much you use depends on what you want to visualize. If you want to create a chart that shows revenue and revenue target, you might want to keep the bar charts. But if the goal is to show revenue compared to revenue target, the single line is much clearer—although it doesn’t allow you to see how much revenue was made.
Jim adds Revenue to Target under Values, FyYearLabel and FyMonthLabel under Axis, and Running 12 Months under Visual Level Filters. He sets the filter to 1 and adds the revenue to the target measure. He turns off Concatenate Labels under X-Axis, and he also turns off the title. Finally, Jim makes sure the sorting of the visual is set to ascending.
Figure 4.50: The initial chart is created.
Next, Jim wants to change the format of the axis. By default, the Display value is set to Millions, but Jim wants a bit more granularity, so he sets it to Thousands.
Figure 4.51: Setting the chart axis format.
Jim also wants the user to be able to clearly see whether sales are above or below target, so he adds a new constant line to the chart. He goes to the Analytics pane and adds a constant line with Color set to red, Value set to 0, and Line Style set to Dotted.
Figure 4.52: The dotted line clearly shows what is above and what is below the target.
Visualization Tip: Labeling Appropriately
In order for a user to differentiate the various reports onscreen, you need to provide appropriate information and label the areas clearly and concisely. As mentioned earlier, it is important that a label not compete with the information being displayed. Using a lighter hue for the text color helps with this.
Jim now adds labels to his visuals. He uses a slightly larger font for the labels and makes the color the same as for the report title. To align them, he uses the alignment tools under the Format tab on the ribbon.
Figure 4.53: The dashboard with labels added.
One of the marketing teams has been working on promoting the top-selling products. Jim wants to create a table that shows the sales for the 10 best-selling products by revenue, their percentage of the total, and their percentage year-over-year growth.
Jim copies the regions matrix in order to automatically get all the same settings on the new matrix. In the new copy, he replaces Region on Rows with DeviceName, and he removes the [$VTT] and [YoY%] measures; however, he can reuse the [Revenue] measure. He notices that DeviceName takes up a bit more space than Region did, so he rearranges all three matrix visuals a bit to make them even again.
Figure 4.54: A visual for devices and revenue.
To get the top 10 devices by revenue, Jim uses the native filtering and ordering. He clicks the arrow at the row header to sort in descending order. To show only the top 10, he can add a special filter at the DeviceName field under Visual Levels Filter that he can configure to show the top 10 based on the [RevenueByPeriod] measure. Jim now sees just 10 products in the visual instead of the entire list.
Figure 4.55: Sorting and filtering the data.
Next, Jim adds year-over-year growth by simply adding the [RevenueYoYByPeriod] measure to the matrix.
Figure 4.56: The top 10 devices by revenue and YoY%.
Now, for the percentage of total, Jim wants to add a new measure to calculate the revenue for one device compared to the revenue for all devices. Jim adds the following measure to calculate the revenue for all devices:
[Revenue all products]=
CALCULATE([Sum of Revenue],ALL('Product'))
|
This measure determines the sum of revenue for all products, regardless of any filters on Product. |
Jim decides that he probably will never use the [Revenue all products] measure directly in his visuals, so he hides it. Now that he has this value, he can divide the sum of revenue for the current product by the revenue for all products:
DIVIDE([Sum of Revenue], [Revenue all products])
|
This measure divides the sum of revenue for the current product by the revenue for all products. |
He then adds the percentage format and sets the number of decimal places to 0. Jim also adds the measure to the visual after the revenue measure. He then makes sure the column headers are Revenue, % to Total, and YoY%. Finally, he adds the label Top 10 Devices by Revenue above the visual. After taking all these steps, Jim sees the results he expects.
Figure 4.57: The top 10 devices report.
The visual showing the top 10 devices YTD clearly indicates that some devices have failed to grow substantially, while others have grown astronomically. The board will want to investigate to determine the reasons behind this.
The board had an important request to be able to see whether a cost reduction has had an effect this fiscal year. Jim decides to show this by displaying the revenue per unit compared to the same revenue per unit in the previous year. He decides to show the numbers in a simple line chart.
Jim already created a [Sum of Units] measure, and now he creates a measure that will divide the revenue by the number of units:
[Revenue by Units]=
DIVIDE([Sum of Revenue],[Sum of Units])
Next, he wants to get the revenue by units from the previous year, so he creates this measure:
[Revenue by Units Previous Year]=
IF(HASONEVALUE('Calendar'[FyYear]),
CALCULATE([Revenue by Units],
SAMEPERIODLASTYEAR('Calendar'[Date])
)
)
Now that Jim has both the revenue by units for the current period and the revenue by units for the previous year, he can calculate the year-over-year growth:
[Revenue by Units YoY%]=
IF([Revenue by Units]>0,
DIVIDE([Revenue by Units]
-[Revenue by Units Previous Year]
,[Revenue by Units Previous Year])
)
|
Because Jim wants to make sure the percentage will be calculated only when there is revenue by units for the current period, he uses the IF function in the calculation. |
Jim also sets the formatting to percentage for this measure. Next, he creates a new chart by copying the Nr of Markets chart that is already on the report. This chart already has the right filter set, so all Jim has to do is remove the region under Legend and the [Nr of Markets] measure under Values and instead insert the [Revenue by Units YoY%] measure.
Jim also adds to this chart the new header Revenue by Unit YoY% Last 12 Months. He finally has a chart that clearly shows that the cost cuts made a difference: Revenue by units was up by over 20%.
Figure 4.58: The chart clearly shows the YoY percentage change over time.
Jim wants to make sure that the board members are able to detect any numbers that they have to take action on.
Visualization Tip: Bringing Attention Where Attention Is Warranted: KPIs
In business intelligence, KPI is almost synonymous with dashboard. A KPI, which stands for key performance indicator, is an indicator that is developed to gauge the success or failure of a metric it is associated with. Originally, companies used KPIs to keep track of the key metrics of the business for a period—even before reports and computers became mainstream. In business intelligence, a KPI is usually associated with a visualization such as a traffic light that shows red, green, or yellow to indicate the status of the metric. This visualization is usually a default control or feature in most data visualization tools, including Excel, Power Pivot, and SQL Server Reporting Services.
I usually prefer to abstain from using KPIs, in the traditional BI sense of the word, in my reports and dashboards. Instead, I rely on other visualization methods to bring attention to parts of a dashboard that need the user’s immediate attention. When you have an entire dashboard littered with red, green, and yellow colors, it becomes very hard to see anything, and it is difficult to bring attention to a metric that is off target and needs immediate attention. When a metric is on target, does it need to draw attention with a big green icon or green background? Usually the answer is no, but sometimes the answer is yes, depending on the importance of the measure or the business requirements.
Remember that the customer is always boss. Some CFOs or dashboard users expect and want their KPIs to be green when the metrics are on or over target. You should give them what they want if they are not open to other ideas.
Jim selects the key metric matrix again, and on the Format pane he turns on Background Color Scales under Conditional Formatting for the YoY% column.
Figure 4.59: Adding conditional formatting.
This turns on the default setting, but Jim wants to fine-tune it, so he clicks Advanced Controls and checks Color by Rules.
Figure 4.60: Applying the formatting rule.
The report now has dynamic formatting applied to any cell that contains a value below zero.
Figure 4.61: The report with dynamic formatting applied to YoY%.
Jim adds the same conditional formatting rules to the other YoY% columns of the report. The result is a dashboard that contains all the metrics Jim wants to show.
Figure 4.62: All the metrics are added to the dashboard.
Jim is now ready to finish the report and clean it up for the final view. To tidy up, he deselects Show Gridlines. This results in a much cleaner version of the report.
Figure 4.63: Hiding the gridlines.
It’s time for Jim to do a last check of the dashboard. He considers the following questions related to factors that make or break a dashboard:
- Are the same fonts used everywhere?
- Do the colors match up?
- Are items that belong together placed together?
- Are the items aligned properly?
- Does the data look correct?
- Do the interactive parts of the dashboard work as expected?
After he has satisfactorily answered all these questions, it’s time for Jim to show the dashboard to some of the key users and get their feedback. Then he can do the work needed to allow his users to drill down into more details by creating detailed reports.