These DAX calculations are known as Measures (in Excel 2013 they are called Calculated Fields). In Power Pivot we can call on the DAX formula language to write our own median formula that we can use inside a PivotTable (see column I below): IFERROR allows me to copy the formula down to row 20, to allow for growth or contraction in the PivotTable that may occur as a result of changes in the Slicer selection, and prevents an error showing in the event of missing data. In the event that the formula returns and error, IFERROR will override it and return nothing, as denoted by the "" at the end of the formula: = IFERROR(MEDIAN(IF(Table1=Pivots!B8,Table1)) ,"") These values are then passed to MEDIAN which simply returns the median value. In English it reads: if the product is Brie, then return the value from the Actual Sales column.
The IF formula returns an array of values from the Table1 Actual Sales column IF the Product column contains the value in B8 (Brie), of the PivotTable. Let’s look at each component of the formula starting with IF: It’s an array formula so it’s entered with CTRL+SHIFT+ENTER, which automatically inserts the curly braces you can see surrounding the formula in the formula bar above. The formula is: = IFERROR( MEDIAN( IF(Table1=Pivots!B8,Table1) ) ,"") I’ve formatted the header in cell D7 with blue fill to make it appear part of the PivotTable, but you if you look in the formula bar you'll see the formula: In the image below you can see my PivotTable is in columns B and C, and I’ve put my median formula in column D. It’s not actually ‘in’ the PivotTable, but rather in a spare column to the right of your PivotTable. We’ll look at calculating the Median in a regular PivotTable first. xlsx files, please ensure your browser doesn't change the file extension on download.
Excel for Customer Service Professionals.
0 kommentar(er)
