Microsoft Office Tutorials and References
In Depth Information
Needless to say, you cannot produce such a report by using any type of PivotTable. But you
can produce the original values needed to compute every single cell by using one or more
PivotTables. Just create some PivotTables that perform the computation and then use
standard Excel formulas to move the original data to the dashboard.
We are not going to show every step you need to follow to build the report. We just want to
focus on the particular characteristics of the dashboard building process. You start by loading
tables from the database in PowerPivot and then build a very simple PivotTable that contains
the basic information. In Figure 8-20, you can see the PivotTable that contains the report
for the reseller sales.
FIguRE 8-20 The original PivotTable that contains reseller sales.
This PivotTable, together with a very similar one built on top of Internet sales, contains the
basic information that you need to build the dashboard. Because this is a PowerPivot
workbook, you might expect to build a very complex DAX formula that magically produces the
dashboard, but this is not the case. PivotTables are useful for exploring data in an interactive
way, but there is no need to push them too far from their nature. After you have data
available, it is an Excel task to move it to the dashboard and perform further computations.
You can base your dashboard on the PivotTable you just created, creating formulas that move
information from the PivotTable to the dashboard. Nevertheless, PivotTables are, by their very
nature, dynamic. They can change their size if new values appear in the source tables. So if you