Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
184 Practical PowerPivot & DAX Formulas for Excel 2010
PivotChart Tools/Design Ribbon
The PivotChart Tools/Design ribbon is shown in Figure 5-73.
Type Group The Change Chart Type button opens the Change Chart Type dialog.
This (apart from its title) is the same as the Insert Chart dialog that you met earlier.
Naturally enough, you can use this to alter the fundamental design of your chart. The
Save As Template button allows you to save a chart as a template—when you create
charts, you can, optionally, base them on previously saved templates (these have a .crtx
file extension).
Figure 5-72 Pivot chart with logarithmic scale
Figure 5-73 PivotChart Tools/Design ribbon
Chapter 5: Pivot Tables and Pivot Charts: Overview 185
Data Group Switch Row/Column is a really cool button. It flips or “pivots” your
pivot chart (and any linked pivot table) by swapping the Legend Fields and the Axis
Fields. You can do the same through the Select Data button.
Chart Layouts Group The Chart Layouts button in this group has a drop-down
menu. Use this to alter the overall appearance of a chart. This is an easy way to add
titles to your charts and reposition the legend.
Chart Styles Group The Chart Styles button in this group opens a drop-down
menu. This is handy for altering the color scheme of your chart.
PivotChart Tools/Layout Ribbon
The PivotChart Tools/Layout ribbon is shown in Figure 5-74.
Current Selection Group Pivot charts have so many options for design that it’s
easy to create a mess! The Reset to Match Style button can undo a lot of harm and
harmonize your design changes. The Format Selection button is an alternative to right-
clicking within the chart and choosing format options from context menus.
Labels Group This group contains five buttons. These are for showing, hiding, and
positioning the chart legend, labels, and titles.
Axes Group Use this group to control both axes and gridlines. The Axes button has
different options from the Axis button in the Labels group. This one has more to do
with position, the latter has more to do with captions.
Background Group The Background group has buttons to format the plot area,
chart walls, and chart floors, and for rotating a chart.
Figure 5-74 PivotChart Tools/Layout ribbon
186 Practical PowerPivot & DAX Formulas for Excel 2010
Analysis Group This group includes a Trendline button. Some of the options for
trend lines are quite advanced—for example, adding exponential trend lines or moving
average trend lines.
PivotChart Tools/Format Ribbon
The PivotChart Tools/Format ribbon is shown in Figure 5-75.
Shape Styles Group The single button, Shape Styles, is context-sensitive. Use this to
format the background of the chart, the legend, or the columns in a column chart.
WordArt Styles Group Use the first drop-down in this group to create amazing
fonts and font colors for the text in your charts.
PivotChart Tools/Analyze Ribbon
The PivotChart Tools/Analyze ribbon is shown in Figure 5-76.
Data Group The Clear button opens a drop-down menu. This menu has two options
for clearing filters on the chart or for clearing all. If you choose Clear All, it will remove
the chart. Please be careful—it will also remove any linked pivot table. If your chart
is linked to a table, then click on the chart and press delete—this removes the chart
without removing the linked table.
Figure 5-75 PivotChart Tools/Format ribbon
Figure 5-76 PivotChart Tools/Analyze ribbon
Chapter 5: Pivot Tables and Pivot Charts: Overview 187
Show/Hide Group The Field Buttons button can be used to selectively remove
buttons from the pivot chart. It also has a Hide All option, which removes all buttons.
To re-establish all buttons, click Hide All again. Try not to use the Field List button—
if you do, you will have a second (and non-PowerPivot) field list for the chart. This
second field list also persists for any linked pivot table.
Insert Ribbon
The Insert ribbon has quite a few groups and buttons. Only the Charts group is
discussed here, as it is the most relevant to chart design.
Charts Group The buttons in this group allow you to change the chart type if the
pivot chart has the focus. It duplicates the functionality of the Change Chart Type
button in the Type group of the PivotChart Tools/Design ribbon. You can also change
the chart type by right-clicking on the pivot chart. If the pivot chart does not have the
focus, the buttons will create a new chart. The diagonal arrow button at the bottom
right opens either the Change Chart Type or the Insert Chart dialog.
That concludes this chapter. You have had an overview of pivot tables and pivot charts,
especially in the context of PowerPivot.
This page intentionally left blank
DAX
Part II
This page intentionally left blank
Fundamental Functions:
Filter, Logical, and
Miscellaneous Functions
Chapter 6
192 Practical PowerPivot & DAX Formulas for Excel 2010
T
his is a wide-ranging chapter on some of the fundamental DAX functions.
It includes syntax and examples for all of the Filter functions and all of the
Logical functions. In addition, it discusses some other functions—for example,
those that are often referred to as Information or IS() functions. An example is
ISBLANK(). There is also one Text function, BLANK(), which has been added here
because of its close relationship to ISBLANK(). The Filter functions, in particular, are
extremely powerful. Among other things, they allow you to override the filter context that
a user chooses in a pivot table. They help to display classic BI values such as “percentage of
parent” or “percentage of all.” Some of the examples are shown as calculated columns,
others as measures. With measures, in particular, your results may differ from those
shown in the screenshots, as there are a few different incarnations of Northwind—not
always with the same data!
C
Key concepts Filtering data, overriding and changing filter context, denormalizing
data, testing data, conditional testing, examining data, dealing with errors
C
Keywords ALL(), ALLEXCEPT(), ALLNOBLANKROW(), AND(), BLANK(),
CALCULATE(), CALCULATETABLE(), DISTINCT(), EARLIER(), EARLIEST(),
FALSE(), FILTER(), IF(), IFERROR(), ISBLANK(), ISERROR(),
ISLOGICAL(), ISNONTEXT(), ISNUMBER(), ISTEXT(), NOT(), OR(),
RELATED(), RELATEDTABLE(), TRUE(), VALUES()
C
Preparation Some of the examples in this chapter (and all the other chapters
on DAX functions) refer to calculated columns created earlier on our Northwind
sample. If you don’t have Northwind data in a PowerPivot model, instructions of
how to import Northwind from SQL Server are given in Chapter 1. If you do not
have SQL Server or SQL Server Northwind, you may want to import the Access
version of Northwind—instructions are in Chapter 2. Alternatively, you can
download an Excel version of Northwind from this book’s Web site—if you do,
you’ll need to set up relationships between the tables, and again, Chapter 2 shows
you how. e calculated columns you’ll need, if you want to try out the examples,
were created in Chapter 1 and Chapter 2. As a reminder, they are the Year column
in the Orders table, the Sales Amount column in the Order Details table, and the
Category column in the Products table. Here are their respective DAX formulas:
=YEAR(Orders[OrderDate])
='Order Details'[Quantity] * 'Order Details'[UnitPrice]
=RELATED(Categories[CategoryName])
ALL() 1/7
ALL() can be used to modify the filter context of a measure. Consequently, the
examples over the next few pages concentrate on measures. In particular, ALL() is
frequently used to modify the filter context for a CALCULATE() function. As such,
Chapter 6: Fundamental Functions 193
many of the following examples are going to use CALCULATE() as well as ALL().
CALCULATE() itself has a separate entry later in the chapter. Before we investigate
further, here are three measures in this example that are always modified by the filter
context. The first is called Num (for numerator), the second Den (for denominator), and
the third NumOverDen (for numerator divided by denominator). All three measures
have been added to the Order Details table. The Employees LastName column is on
the Row Labels, and there are slicers on Year and Category.
ALL() is a Filter function. The examples are measures. Your figures may differ if you
have a different release of Northwind from the one used here.
Syntax
=SUM('Order Details'[Sales Amount])
=SUM('Order Details'[Sales Amount])
=SUM('Order Details'[Sales Amount])/SUM('Order Details'[Sales Amount])
Result
Analysis
If you slice on Year and/or Category, notice how the numerator and denominator both
change. They reflect the current filter context. As they are both equal, the third measure
is always 1. With measures, it is often a requirement that you allow the numerator and
denominator to behave differently. This is useful for calculating ratios in particular.