Post date: Aug 3, 2018 9:32:49 PM

Lesson Objectives

At the end of this lesson you will be able to:

• • Describe sections of an interactive output chain

  • Add tables to the Data Model

  • Create a Measure in the Data Model

What is an Interactive Output Chain?

The benefits of an output chain are:

• • It is intended to display only salient information without distracting detail or calculation complexity

  • It can enrich results with human friendly descriptions that would only clutter calculations.

  • If formats results to give users at-a-glance insights into our model

  • It provides users space to 'play' with results without the possibility of corrupting our model's calculations.

Display Salient Information

Process tables often contain intermediate calculations. Their results are not the results customers want to see. Process tables also contain detail that is useful when summarized and just noise otherwise.

Enrich Results

Process tables do not need long item names or descriptions for calculations. They only need item IDs and properties. But people need them. When we display our calculation results to users it is often best to replace IDs with names and descriptions.

At-A-Glance Insights

The purpose of graphs is to make important data values and relationships obvious. Sorted graphs can instantly show us which items are most profitable or which factors are most impactful.

'Play' with Results

Interacting with results can be enjoyable. We get a sense of control and power over the model when we can sort it, filter it, or rearrange it. These interactions aren't just fun, they can help us better understand our model and focus on important factors.

Our challenge is to turn data into information that is meaningful to users. The traditional approach is to create static summaries like profit and loss statements. But we can go beyond static reports and create interactive analysis tools by making them dynamic through sorting and filtering. Excel provides four features that, when chained together, make this possible: the Data Model; PivotTables; Slicers; and Pivot Charts.

Data Model

Benefit: Combine data from different tables

Allows us to link data from one table into another table much like we do with VLOOKUP() and INDEX()/MATCH() but without formulas. We are not limited to just two tables. The data model can handle several different tables linked to each other. Data model's can also include measures. Measures replace PivotTable's calculated field sets with a much more powerful library of functions.

The data model is a very effective way to dramatically reduce formula count.

PivotTables

Benefit: Summarizes data in a way that can be sorted, filtered, and rearranged by users

PivotTables summarize raw data and group results by categories. A single PivotTable can replace a sea of formulas which reduces model risk. For that alone they are valuable modeling tools. But PivotTables have several other extremely important features including the ability to sort, filter and drilldown into the source data.

Slicers

Benefit: Filter multiple connected PivotTables simultaneously

Once we have one or more PivotTables we can add Slicers to them. Slicers filter PivotTable results. They give users an easy and intuitive way to play with PivotTables with filtered results reflected in associated Pivot Charts.

Filtering our process table's results to only certain records helps focus on things of interest. We can link a single slicer to several PivotTables that use the same data source allowing us to see different views of a single item, or group of items, simultaneously.

Pivot Charts

Benefit: Provide at-a-glance insights into our model

Pivot Charts present PivotTable numbers graphically revealing data relationships that might not otherwise be noticed. Most people prefer graphs over a sea of numbers. Using Pivot Charts adds an interactive executive dashboard component to models.

How To Create a Data Model with Measures

Excel's advanced features gives us two formula alternatives for linking data from multiple tables: Power Query and the Data Model. For this lesson we will use Excel's Data Model. There are several ways to get our Excel tables into Excel's Data Model but because this is about using Excel's advanced features we are going to use the Data Model from within Excel's Power Pivot feature just so we can get some exposure to this amazing tool.

Creating a Data Model is simple with Power Pivot. Just follow these steps:

• 1. Click any cell in a table.

  2. Use Excel's Ribbon. Click Power Pivot > Add to Data Model icon

• 1. Link the two tables by clicking on a field that the related tables have in common and dragging it to the other table.

• 1. This displays the Measure dialog. Here we provide the measure's base table, name, description, and formula.

• 1. Click the fx button to display a list of formulas we can use

• 1. After selecting the function we want we can complete the formula with the aid of Power Pivot's Intellisense

At this point the measure displays in the PivotTable's field list like any table field.

Lab

• 1. Open your model and add tblProcess and tblLP to the data model

  2. Link them by fields tblProcess[Loan '#] and tblLP[ID]

  3. Create measure Fees that combines both fixed and percentage based fees. In the Measure dialog:

     1. Select tblProcess in Table Name: dropdown.

     2. Enter "Fees" in the Measure name box.

     3. Enter "Total Fees" in the Description: box

     4. Click the fx button and select

        1. Math & Trig in the Select a category dropdown

        2. Select SUMX in the Select a function dropdown.

     5. For SUMX's Table parameter, use the Measure dialog's intellisense to select tblProcess

     6. For SUMX's Expression parameter enter

     7. tblProcess[Percentage Fees] + tblProcess[Fixed Fees]

     8. Click Check Formula to make sure "No errors in formula" are found.

     9. Set Formatting Options to:

• • • 1. Category: Number

      2. Format: Decimal Number

      3. Decimal Places: 2

• 3. 10. Click OK and close the Measure dialog.

  4. Save your model.

Assignments

• 1. Read Create Relationships in Diagram View

  2. Read Create a Measure in Power Pivot

  3. Read Measures in Power Pivot

  4. Read DAX Functions Quick Guide

  5. Read SUMX Function

  6. Complete the lab

  7. Complete the quiz

Quiz

• 1. Power PIvot is the only way to add tables to the data model

     1. TRUE

     2. FALSE

  2. How does the Data Model reduce model risk?

• • 1. The Data Model cannot cause data synch errors like formulas can

    2. There are fewer things to go wrong with the Data Model

    3. All of the above

    4. None of the above

• 3. How does the Data Model speed development?

• • 1. It's new and more advanced

    2. We can link multiple tables quicker

    3. It doesn't

Summary

The data model is the first link in the Output Chain. We can use it to replace computer friendly IDs with human friendly names and descriptions. We can also use it to add measures where one measure can replace a column of formulas; thereby, reducing our model's risk.