Pinterest Pixel

COUNTROWS in Excel Power Pivot

  In Power Pivot, one of the major and more powerful feature are Measures. Measures (also known as... read more

Download Excel Workbook
Bryan
Posted on

Overview

COUNTROWS in Excel Power Pivot | MyExcelOnline COUNTROWS in Excel Power Pivot | MyExcelOnline

 

In Power Pivot, one of the major and more powerful feature are Measures.

Measures (also known as Calculated Fields in Excel 2013) are formulas/calculations that are added to a Pivot Table.

We will work on a simple example to show you how easy it is to use the COUNTROWS function in your Measure.

COUNTROWS in Excel Power Pivot | MyExcelOnline

download excel workbook Countrows.xlsx

 

STEP 1: Select the Sales Table. Go to Insert > Table.  Click OK.

COUNTROWS in Excel Power Pivot

 

STEP 2: Go to Table Tools > Design > Table Name  and give your new Table a descriptive name.  In our example, we will name it Sales

COUNTROWS in Excel Power Pivot

 

STEP 3:

In Excel 2013 & 2016

Select your Sales Table. Go to Power Pivot > Add to Data Model.

This will import your new Table into the Power Pivot Window.

COUNTROWS in Excel Power Pivot

In Excel 2010

Go to PowerPivot > Create Linked Table.

Linking Excel Tables in Power Pivot

 

STEP 4: This will open the Power Pivot Window.

The Sales Table will now be automatically loaded to the Power Pivot Data Model.

See also  Using the Diagram View in Power Pivot

COUNTROWS in Excel Power Pivot

Now Close the Power Pivot Window. 

Using Measures in Power Pivot

 

STEP 5: Go to Insert> Pivot Table.

Using Measures in Power Pivot

In Excel 2016

Select Use this workbook’s Data Model.  This will use the Data Model you just uploaded in the last step.

Select Existing Worksheet and choose your location for your Pivot Table and press OK.

COUNTROWS in Excel Power Pivot

In Excel 2013

Go to Use External Data Source > Choose a Connection

Using Measures in Power Pivot

Now select Tables > This Workbooks Data Model > Open:

Using Measures in Power Pivot

In Excel 2010

Go to Use an External Data Source > Choose Connection:

Using Measures in Power Pivot

Now select PowerPivot Data > Open:

Using Measures in Power Pivot

 

STEP 6: Adding a Measure:

In Excel 2016

On the Sales Table, right click and select Add Measure.

COUNTROWS in Excel Power Pivot

An alternative way in Excel 2016 is go to Power Pivot > Measures > New Measure.

Using Measures in Power Pivot

In Excel 2013

Go to PowerPivot > Calculated Fields > New Calculated Field

(In Excel 2013 “Measures” were renamed “Calculated Fields” and returned to “Measures” in Excel 2016…I know, how annoying!)

Using Measures in Power Pivot

In Excel 2010

See also  Comment In Query Steps In Power Query

On the Sales table, right click and select Add New Measure.

Using Measures in Power Pivot

An alternative way in Excel 2010 is to select a cell inside the Pivot Table that was created in the previous step and go to Power Pivot > New Measure:

Using Measures in Power Pivot

 

STEP 7: This is where we create our Measure using the COUNTROWS function.

For Measure Name, type in any name that you like e.g. Number of Sales

For the Formula, after the = sign start typing the word COUNTROWS.

Just like in native Excel, this will bring up the Formula helper and choose the COUNTROWS function by either double clicking on the blue highlighted COUNTROWS option or by pressing the Tab keyboard to confirm this suggestion:

COUNTROWS in Excel Power Pivot

 

After the COUNTROWS formula is selected, type in the Table name that we created in Step 2, which we called Sales.

This will bring up the Formula helper and within here you need to select the Sales Table option and close the parenthesis:

See also  Installing Power Pivot in Excel 2010

COUNTROWS in Excel Power Pivot

 

Set the Category as General, and this will automatically handle the formatting for you.  Press OK to confirm this.

This Measure will now return the Total Number of Rows based on the Pivot Table we will be setting up in the next step.

COUNTROWS in Excel Power Pivot

 

STEP 8: Place your new Measure Number of Sales in the Values area.

Then place Month in the Rows area

COUNTROWS in Excel Power Pivot

 

STEP 9: Now we are able to use our new COUNTROW Measure in the Pivot Table.

COUNTROWS in Excel Power Pivot

 

 

PIVOT BANNER

If you like this Excel tip, please share it
COUNTROWS in Excel Power Pivot | MyExcelOnline COUNTROWS in Excel Power Pivot | MyExcelOnline

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...