COUNTROWS In Excel Power Pivot


COUNTROWS In Excel Power Pivot

Measures is amongst the most important and highly powerful features in Power Pivot. Measures are actually the calculations or formulas you add to the Pivot Table. Below is an example of it.

First of all, you need to create a table. For example, a sales table

Once you have created it and filled the values in it, you need to select it and follow

Insert >Table

and click OK

 

Now

Table Tools > Design > Table Name and write whatever name you want.

 

Select the table and follow

Power Pivot > Add to Data Model.

Your table will be imported to the Pivot Window.

 

If you are using Excel 2010, you need to follow

Power Pivot > Create Linked Tables

 

Next step is

Insert > Pivot table

If you are using excel 2016, you need to select Use This workbook’s data model. It will use Model you have just uploaded.

Click of Existing Window and select the location.

 

If you are using excel 2013, you need to follow

Use External Data Source > Choose a connection

And later select Tables > This Workbooks data model > Open

 

If you are using excel 2010, you need to follow

Use External Data Source > Choose > connection

After that select Power Pivot Data > Open

 

The next step is adding the measures.

If you are using Excel 2016 and 2010 , Right click on Sales and click on Add Measure

If you are using Excel 2013, PowerPivot > Calculated Fields > New Calculated Field

 

This is where the actual measure is created. Use the function COUNTROWS to get it done. Type any name of the measure you want. After that write the formula starting with COUNTROWS.

 

Once you have selected the formula, type name of table, named as Sale above.

 

Category must be set to General so that formatting is handled automatically. Click OK. It will now tell you the amount of rows.

 

Now you need to place the new measures in Values area and also add month in Rows area.

Now you are all set to use the COUNTROW Measure in Pivot Table.