1Mashing Up Data with PowerPivot*When “Filter, Then Calculate”…

1Mashing Up Data with PowerPivot*When “Filter, Then Calculate” Does Not Work in DAX MeasuresThis time you need to open the file with the worksheet Sls and have it linked to thePowerPivot window, using Add to Data Model as in the last hands-on assignment.1. Please create a pivot table first showing the sum of sales by each representativeon each date. Now the task is made so easy. However, if you want to comparethe 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 anexpression and then one or more filters. For those filters, you are going to use aspecial function called ALL. ALL says that you want it to look not just at onerepresentative’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 newmeasure (Hint: right click on Table1 to select Add Measure) by using=SUM(Sls[Sales])/Calculate(SUM(Sls[Sales]), ALL(Sls)). The pivot table showsthat % of Grand Total for Bill’s sales of $851 on June 2, 2011 represents 0.9percent of the grand total sales.3. Now you want to calculate how Bill’s $851 sale on June 2 compared to all saleson June 2. The numerator of the DAX measure is =SUM(Sls[Sales]). Thedenominator is going to be hard. Instead of ALL(Sls), you need to ask forAllExcept(Sls, Sls[Date]). It means go ahead and throw out all the filters exceptfor the Date filter. Keep filtering by date. Please create a new measure, % ofDaily Sales, by using the formula2=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.4. You can also override the filters by specifying other filters in the CalculateFunction. The actual syntax of the Calculate function is Calculate (Expression,[filter 1], [filter 2], [filter 3], ….). Please create a new measure, Amber Sales, tocalculate 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 showeveryone’s sales as a percentage of Amber’s Sales.=SUM(Sls[Sales])/Calculate(SUM(Sls[Sales]), Sls[Rep]=”Amber”) shows sales asa percentage of Amber’s total sales for that day. Please create such a newmeasure34Mix in Time Intelligence FunctionsYou can apply many filters in the Calculate function. You can replace the first argumentin Calculate with MAX, MIN, AVERAGE, or any function. There are 34 Time Intelligencefunctions. If you want to calculate a running month to date (MTD) total, you can use theCalculate function and specify a filter of DatesMTD(Sls[Date]). But only for reps thatmatch, use AllExcept(Sls, Sls[Rep]).1. To show MTD sales for each rep, create a new measure, MTDThisRep, using theformula =Calculate(SUM(Sls[sales]), DatesMTD(Sls[Date]), AllExcept(Sls,Sls[Rep]))2. You can also use previously defined calculated fields to simplify the calculationfor another measure. To get to This Rep’s Percentage of MTD Sales versus allMTD Sales, first, you need to build MTDThisRep (Already exists). Next, you needto build MTDAllRep usingCalculate(Sum(Sls[Sales]),DatesMTD(Sls[Date]),All(Sls)). Now you can simplybuild the formula =Sls[MTDThisRep]/Sls[MTDAllRep] as shown in the CalculatedField Settings image below. Please check a book for a complete list of TimeIntelligence Functions53. Save and close your Excel file. Half of your completed data table should look likethis:6Please make sure that all the data columns are properly formatted, all your createdmeasures are activated (with a check mark in front of each). The Excel 2016 program onmy computer is a little wired – Sls is only shown as the data model name, not as thetable name in the field list. So far as the linked table name in Power Pivot is Sls, theresults should not be affected.Combination LayoutsThe PivotTable drop-down in the PowerPivot Window offers eight choices: If you plan toconvert the pivot table to values to reuse it, choose a flattened pivot table which is onlya few clicks along the way. A combination report offers one or more pivot tables orcharts. Note that for each chart on your layout, Microsoft inserted a new worksheet tohold the actual pivot table for the chartThe following steps show how to replicate the layouts shown in the blogs and press:71. Open the Excel file BlogsAndPress. Link it to PowerPivot Window. (Hint: Pleasereview the section on adding calculated columns using DAX. You have to addtwo new columns – Month and Year to Table1.)2. Once you added two columns Month and Year to your linked data table, youneed to select Four Chart option from Pivot Table menu in PowerPivot window.3. Select New Worksheet instead of Existing Worksheet in the dialog box.4. If it does not work well the first time, choose a location rather than letting themdefault. Choose a spot on Row 5 of the new worksheet (Sheet1 probably). Youcan always drag a chart structure to wherever you want on the worksheet later aswell.5. Go back to Power Pivot window and select Four Chart option for ExistingWorksheet, but Sheet1 location data should show in the text box.6. Add as many slicers as possible to the top and left of the chart.7. Look into the charts image on the next page. Simple bar chart on top of the piechart shows the values of Year and Month. Since these two variables are on thesame level, you need to move them into the same Axis field as shown in the fieldlist. The bars should represent the Count of Source. Feel free to use the crossbutton to change the chart element and the brush button to change the bar coloras you like.8. The pie chart shows Count of Type values by Source.9. For Media Type, you need to choose Stacked Area from chart models to use. It iscreated by selecting Type, Month and Year from the Field list. The formula isType * Year and Month. Legend of the chart should use Type as well.10. The last chart uses the Stacked Bar Chart model. Webinar belongs to Type. The3Rd Party is a Source. Thus, it should be created using Type (on Vertical Axis) bySource (as Values), using Source as legend. You may still need to fine tuneeach chart to make it look identical to the one in the image.811. 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 interestinggraphic to Row 1.12. Add an interesting graphic below the charts to balance the graphic on top of thecharts.13. Go to file, Options, advanced, Display Options for This Worksheet. Clear theGridlines check box. If you want to go all out, scroll up and clear the scroll bars,sheet tabs, and formula bars.14. Minimize the ribbon.15. Add a fill color behind the whole worksheet.16. 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.17. Click away from the pivot table.You’ve reached the end of this long assignment. If you ever changed any data in atable, the changes may not be reflected in the pivot table immediately. However, in thetop part of the PowerPivot Field List, you may see a warning message to refresh therelevant pivot table and please do it accordingly. If you see the values shown in a pivottable different from those in a given image, examine the measure(s) used and therelationship between the tables in PowerPivot window. They might be the sources oferrors.9When you complete this assignment, please save both data files, each using the originalfile name and your name initials as the new file name (the pattern should be). Submit both files to the relevant assignmentsubmission dropbox in Learn 9._______________________*: This assignment is adapted from Chapter 10 in Pivot Table Data Crunching: Microsoft Excel 2010written by Bill Jelen, Michael Alexander, 2013, Que drafting, Indianapolis, IN, USA.