Home > Articles > Computer Software > Business Office Software > Spreadsheet Software > Microsoft Excel

  • Print
  • + Share This
From the author of Using CountRows and Distinct

Using CountRows and Distinct

If you need to figure out the average revenue per store, it helps to know how many distinct stores make up the revenue number. A formula such as =COUNTROWS(distinct(demo[StoreID])) will do the trick.

Revenue Per Store is then calculated as =sumx(demo,demo[Revenue])/demo[NumberStores].

Note that measures appear in the field list with a calculator icon next to them.

Using the Value and Filter Functions

The other cool thing about measures is that they automatically respect all filters applied to a cell in a pivot table, but with a little magic, you can build a calculation that partially ignores the filters!

First, consider how many filters are applied to cell C15 in the following figure?

Certainly, the three slicers are these filters:

  • FY=2009
  • DM=Laura
  • Division=Belts

So, are there only three filters? Actually, there are five filters on that one cell. The other two filters are the following:

  • Quarter=Q1
  • Region=Illinois

By default, the formula for a measure does not have to mention FY, DM, Division, Quarter, or Region. All those filters will be applied to the calculation.

Sometimes, you might want to perform a calculation where the divisor ignores one of the filters. Perhaps you want to show sales of belts as a percentage of handbags.

For those of you familiar with SUMIF or SUMIFS in Excel, the CALCULATE function in DAX does a similar operation. You can CALCULATE(SUM(demo[Revenue],<filter1 to apply or override>,<filter2 to apply or override>,<filter3 to apply or override>).

In the following example, the formula in D14 calculates revenue for 2009, Laura, all quarters, Atlanta. It forces PowerPivot to ignore the slicer selection for division (currently belts) and instead forces the division to be handbags.

In a similar fashion, you could override a time dimension to force PowerPivot to use the previous year's data.

There are many examples in ehivh DAX Measures can save the day. I spent a long chapter in PowerPivot for the Data Analyst going through more examples.

  • + Share This
  • 🔖 Save To Your Account