Mashing Up Data with PowerPivot* (Maximum 50 Points)

 

When “Filter, Then Calculate” Does Not Work in DAX Measures

 

This time you need to open the file with the worksheet Sls and have it linked in the PowerPivot

window.

1. Please create a pivot table first showing the sum of sales by each representative on each date. Now the task is made so easy. However, if you want to compare the sales to the

total sales for a month, you need to do some calculation. In DAX, instead of using

SUMIFS, you need to use Calculate. Calculate asks for an expression and then one or

more filters. For those filters, you are going to use a special function called ALL. ALL

says that you want it to look not just at one representative’s sales for a particular date,

but all the sales in the table.

2. Suppose you want to see % of Grand Total sales. You need to create a new calculated fields by using =SUM(Sls[Sales])/Calculate(SUM(Sls[Sales]), ALL(Sls)). The pivot table

shows that % of Grand Total for Bill’s sales of $851 on June 2, 2011 represents 0.9

percent of the grand total sales.

 

 

3. Now you want to calculate how Bill’s $851 sale on June 2 compared to all sales on June 2. The numerator of the DAX measure is =SUM(Sls[Sales]). The denominator is going to

be hard. Instead of ALL(Sls), you need to ask for AllExcept(Sls, Sls[Date]). It means go

ahead and throw out all the filters except for the Date filter. Keep filtering by date. Please

create a new calculated field, % of Daily Sales, by using the formula

=SUM(Sls[Sales])/Calculate(SUM(Sls[Sales]),AllExcept(Sls,Sls[Date])). Bill’s $851 sales

is now 25% of the daily sales on June 2, 2011.

 

 

2

 

4. You can also override the filters by specifying other filters in the Calculate Function. The actual syntax of the Calculate function is Calculate (Expression, [filter 1], [filter 2], [filter

3], ….). Please create a new calculated field, Amber Sales, to calculate all Amber’s

sales. The Calculated Field formula should be

=CALCULATE(SUM(Sls[Sales]),Sls[Rep]=”Amber”).

5. If Amber is the sales star in the store, perhaps you would want to show everyone’s sales as a percentage of Amber’s Sales. =SUM(Sls[Sales])/Calculate(SUM(Sls[Sales]),

Sls[Rep]=”Amber”) shows sales as a percentage of Amber’s total sales for that day.

Please create such a new calculated field

 

 

 

Mix in Time Intelligence Functions

 

You can apply many filters in the Calculate function. You can replace the first argument in

Calculate with MAX, MIN, AVERAGE, or any function. There are 34 Time Intelligence functions.

If you want to calculate a running month to date (MTD) total, you can use the Calculate function

and specify a filter of DatesMTD(Sls[Date]). But only for reps that match, use AllExcept(Sls,

Sls[Rep]).

1. To show MTD sales for each rep, create a new calculated field, MTDThisRep, using the formula =Calculate(SUM(Sls[sales]), DatesMTD(Sls[Date]), AllExcept(Sls, Sls[Rep])).

 

 

3

 

 

 

2. You can also use previously defined calculated fieldsto simplify the calculation for another measure. To get to This Rep’s Percentage of MTD Sales versus all MTD Sales,

first, you need to build MTDThisRep (Already exists). Next, you need to build MTDAllRep

using =Calculate(Sum(Sls[Sales]),DatesMTD(Sls[Date]),All(Sls)). Now you can simply

build the formula =Sls[MTDThisRep]/Sls[MTDAllRep] as shown in the Calculated Field

Settings image below. Please check a book for a complete list of Time Intelligence

Functions

 

 

4

 

 

3. Save and close your Excel file. Half of your data table should look like this:

 

 

 

5

 

Combination Layouts

 

The PivotTable drop-down in the PowerPivot Window offers eight choices: If you plan to convert

the pivot table to values to reuse it, choose a flattened pivot table which is only a few clicks

along the way. A combination report offers one or more pivot tables or charts. Note that for each

chart on your layout, Microsoft inserted a new worksheet to hold the actual pivot table for the

chart

 

 

The following steps show how to replicate the layouts shown in the blogs and press:

 

1. Open the Excel file BlogsAndPress. Link it to PowerPivot Window. (Hint: Please review the section on adding calculated columns using DAX. You have to add two new columns

 

 

Report Formatting

 

 

 

6

 

– Month and Year to Table1.)

2. Once you added two columns Month and Year to your linked data table, you need to select Four Chart option from Pivot Table menu in PowerPivot window.

3. Choose a location rather than letting them default. Choose a spot on Row 5 of the new workbook.

4. Add as many slicers as possible to the top and left of the chart. 5. Look into the charts image on the next page. Simple bar chart on top of the pie chart

shows the values of Year and Month. Since they are on the same level, you need to

move them into the same Axis field as shown in the field list.

6. The pie chart shows Source values only.

7. For Media Type, you need to choose the correct chart model to use first. It is created by Type *Month and Year. Look to the right, Legend should use Type as well.

8. The last one uses the stacked bar chart model. Webinar belongs to Type. 3Rd Party is a Source. Thus, it should be created using Type (Vertical) by Source (Horizontal). You may still need to fine tune each chart to make it look identical to the one in the image.

9. Now you are ready to make the data display look better. Make Row 1 very tall, perhaps 270 to 300 points tall. Use Insert, Screenshot to add an interesting graphic to Row 1.

10. Add an interesting graphic below the charts to balance the graphic on top of the charts.

11. Go to file, Options, advanced, Display Options for This Worksheet. Clear the Gridlines check box. If you want to go all out, scroll up and clear the scroll bars, sheet tabs, and

formula bars.

12. Minimize the ribbon.

13. Add a fill color behind the whole worksheet.

14. Although the pivot table is active, click on the bounding box around each slicer. Right- click on the border. Select properties. Select Move and /Size with cells.

15. Click away from the pivot table.

 

 

 

7

 

 

You’ve reached the end of this long assignment. If you ever changed any data in a table, the

changes may not be reflected in the pivot table immediately. However, in the top part of the

PowerPivot Field List, you may see a warning message to refresh the relevant pivot table and

please do it accordingly. If you see the values shown in a pivot table different from those in a

given image, examine the measure(s) used and the relationship between the tables in

PowerPivot window. They might be the sources of errors.

 

When you complete this assignment, please save both data files into a zip folder, each using the

original file name and your name initials as the new file name (the pattern should be

<FileName_YourName Initials>). Submit the zip folder to a relevant assignment submission

dropbox in Learn 9.

_______________________

 

*: This assignment is adapted from Chapter 10 in Pivot Table Data Crunching: Microsoft Excel 2010

written by Bill Jelen, Michael Alexander, 2013, Que Publishing, Indianapolis, IN, USA.