PowerPivot 5 – Measure for Measure

By Stuart Box

Excel Power BI Resources PowerPivot

“Our doubts are traitors, and make us lose the good we oft might win, by fearing to attempt.” ― William Shakespeare, Measure for Measure

Creating Measures

A Measure is a formula that is created specifically for use in the Values area of a PivotTable (or PivotChart), very similar to Calculated Fields in a normal Excel Pivot Table.  However, in PowerPivot a Measure uses DAX functions.  The Values area of a Pivot Table will always require some kind of aggregation and the DAX aggregation functions are very similar to the Excel Functions SUM, MIN, MAX, AVERAGE and COUNT A. There are two type of Measures used in the Values area; Implicit Measures and Explicit Measures.

Implicit Measures

These are created for you by PowerPivot when you drag and drop a numeric field into the Values area, just like a normal Excel PivotTable.  To change the function, use the dropdown in the Values area and select “Summarize by”.

Explicit Measures

These are calculations that you create for yourself by using DAX formulas and functions. To create an Explicit Measure, the easiest way is to right click on a table name in the PowerPivot Field List and select “Add New Measure….”  Choose the table whose data you’re evaluating e.g. if you want to analyse wine sales, you would right click on the WineSales table in the PowerPivot table field list. Give your measure a name (All PivotTables) and then enter your DAX expression. The other way to create an Explicit Measure is to use the Calculation Area of the PowerPivot Window.  Click into the column that is being used in the calculation and then enter your measure into the formula bar, preceded with the name of the measure followed by a colon, as shown below. When you define an expression for a measure using DAX, nothing happens until you drop the measure into the Values area of a PivotTable (this happens by default if you create a measure in the PowerPivot field list).

Using DAX Functions in PowerPivot Measures

In DAX, the five normal aggregation functions (SUM, MIN, MAX, AVERAGE and COUNT) only accept a single column as an argument i.e. you can only sum or find the average of a single column.

SUMX

DAX provides us with more powerful aggregation functions i.e. SUMX, AVERAGEX, COUNTX, MINX and MAXX.  What these functions allow you to do is first perform a calculation on your data, using any number of columns from your tables and then find an aggregation of the result of the calculation (e.g. the sum or average).  These functions have two arguments:- 1. The table where the fields are that you want to use in your calculation. 2. The calculation itself. For example, in our WineSales table, we have a field for the number of CASESSOLD.  The PRICEPERCASE is in the Wines table as shown in the Field List so you will need to use the RELATED function (just as in a calculated column). The expression will look like this:- TotalSales= Sumx(winesales,[CASESSOLD]*related(Wines[PRICEPERCASE])) Note: SUMX can also be used in Calculated Columns on the “One” side of your relationships to find totals for the “Many” side.   Use the RELATEDFUNCTION for the Table argument e.g.:-

Using Measures to Create Measures

Once you create a Measure, you can use it to create more Measures for example to calculate VAT  (surround your measure name with square brackets)  eg: VAT=[TotalSales]*0.2 TotalWithVAT=[TotalSales]+[VAT]

CALCULATE

The CALCULATE function overcomes a major shortcoming of the PivotTable.  Any filters applied to the Pivot Table will be applied to ALL the cells in the Pivot Table Values area.  Sometimes you want different values in the Pivot Table filtered differently or you want to compare ALL your data against filtered data. Essentially the CALCULATE function allows you to apply filters to your data that modify the filters applied through any of the normal Pivot features (i.e. Row and Column fields, the filters on the Pivot Table itself and any Slicers).     The CALCULATE function has the following syntax:- CALCULATE(expression, filter1, filter2….) The expression argument of the function is either a measure or an aggregation (i.e using a nested aggregate function like (SUM or SUMX).    The filter argument is often an “equals to” or “greater than” expression or it can be a function that expresses a filter (e.g. the Time Intelligence Functions explained later in this blog). Let’s take a scenario.  In our WineSales example, we’ve recorded which SalesPerson made each sale of wine.  We may want to compare the sales of one of these SalesPeople( for example, the Salesperson called “Abel”) against the total sales of wine,.  We could use “Show Values As” option on the PivotTable Options tab and show % of Parent Row Total as show here. We can see the Able sold 14.66% of our total Bordeaux sales, and 12.83% of Champagne etc.  But what happens when we filter out “Abel”?  Now we ONLY see Abel’s figures (e.g. sales of Bordeaux represents 9.56% of Abel’s sales NOT the total sales). However if we use CALCULATE, we can see Abel’s sales alongside Total Sales and therefore calculate Abels’ percent of total sales. The expressions would look like this: AbelSales=CALCULATE([TotalSales],SalesPeople[SALESPERSON]="abel") AbelPerc=[AbelSales]/[TotalSales]. Another use of CALCULATE is to remove all filters and return values for ALL your data.  We can use the ALL function as a filter argument e.g. TotalAllSales=CALCULATE([totalsales],ALL(winesales)) or PercentGrandTotal=[TotalSales]/CALCULATE([TotalSales],all(WineSales)) Here are some more examples of the CALCULATE function :-   =CALCULATE(sum([CASESSOLD]),WineSales[SALESPERSONID]=1) Would show total cases sold for Sally =CALCULATE(sumx(WineSales,[CASESSOLD]*related(Wines[PRICEPERCASE])),Wines[PRICEPERCASE]>=75) =CALCULATE([TotalCases],Wines[PRICEPERCASE]>=75) Where TotalCases is a measure

BLANK()

Use the BLANK() function to remove “Divide by Zero” errors (#NUM!) where there’s no data (e.g. for TotalSales) e.g.:- =if([totalsales],[AbelSales]/[TotalSales],blank())

RANKX

Use the RANKX function to rank in descending order of value.  The simplified syntax is:- RANK(Table,Expression) e.g. SalesRank=RANKX(all(SalesPeople[SALESPERSON]),[TotalSales])

IF

To use IF on fields placed in the Row or Column label of the Pivot Table (for example to find Bordeaux wine), use this expression:- =if(DISTINCTCOUNT(WineSales[WINEID])=1,IF(distinct(wines[wine]) ="bordeaux","Y", "N" ),"N" ) This is our penultimate PowerPivot post in this course. Next time we'll look at Time Intelligence Functions, KPI's and Sets (Oh my!). Burningsuit provide Training and Consultancy on PowerPivot and all aspects of Excel and Microsoft applications. We specialise in on-site or on-line instructor lead Training. Contact us to find out how we can help you and your business or organisation be more productive and effective.

Leave a Reply