Open In App

Table Visualization in Excel Power View

Last Updated : 22 Sep, 2023
Comments
Improve
Suggest changes
Like Article
Like
Report

For whatever visualization we decide to make with Power View, we start by generating a Table, which is the default, and then quickly convert the Table to other visualizations. 

The Table is formatted similarly to any other data table, with columns representing fields and rows representing data values. To pick which fields to display in the Table, use the Power View Fields list to select and deselect fields. Fields from the same data table or multiple related data tables might be used. 

Power View in Excel

Power View is an interactive data visualization, and presentation experience that encourages intuitive ad-hoc reporting. Power View is a feature of Microsoft Excel 2013 and of Microsoft SharePoint Server 2010 and 2013. You can enable Power View and use Power View in Excel.

Let's discuss Table visualization.

Table Visualization

You have to start by creating a Table first for every visualization that you want to create on a power view sheet. You can then switch among the Visualization to find the one that best suits your data.

Once you create a Table Visualization that is the default, you can convert it into any other visualization .

Switch Table Visualization

Step 1: Click on the Table Visualization

Click on the Table visualization. Two tabs, POWER VIEW, and DESIGN appear on the Ribbon.

Step 2: Click the Design Tab.

You can choose any of the options present in the Switch Visualization group on the Ribbon.

  • Matrix Visualizations
  • Card Visualizations
  • Chart Visualizations
  • Map Visualization

Creating Table Visualization

Following are the steps to be followed to create a table visualization:

Step 1: Do the steps to build a Table in Power View:  Select the Power View region. In the Power View Fields list, click on the table - Range.  Choose from the options of Country, Events, Seasons, and Year.

power-view-fields
Power-View Fileds

Step 2: As you can see, a table with selected fields as columns and real values will be presented in Power View.

data-table
Data-Table

Adding a Table as Count Field

Assume you wish to show the Season Count as a column. You may achieve this by adding the Session field to the Table as Count. In the Power View Fields list, click the arrow next to the Season field. From the dropdown list, choose to Add to Table as Count.

choosing-add-to-table-as-count
Add to Table

The Table will receive a new column Count of Season, which will display the Season Count values.

count-of-season-displayed
Count-of-Season

Adding a Count Field to Table

 When your data has more than 10000 rows, adding the field Season as Count to the Table is inefficient because Power View must do the computation every time you alter the layout of the Table. Adding a computed field to the Season data table in the Data Model is a more effective method.

Step 1: On the Ribbon, select the PowerPivot tab. In the Data Model group, choose Manage. The Data Model's tables will be presented.

selecting-data-model
Select-Data-Model

Step 2: Select the Results tab. In the Results table, in the calculation area, in the cell below the Season column, enter the DAX (Data Analysis Expressions) formula shown below:

Season Count: =COUNTA([Season])

The Season count formula is shown in the formula bar. When you Hover your mouse over a Season, then it will show the Season Count i.e.,16 in this case.

COUNTA-Function
COUNTA Function

Step 3: Again, you will get a Power View notification indicating that the Data Model has been altered, and if you click OK, the changes will be reflected in your Power View. Select OK.

selecting-ok
Power-View

Step 4: You may see the following in the Power View Fields list. Season Count is a new field added to the Range table. Season Count has a calculator icon next to it, suggesting that it is a calculated field. Choose the following options: Country, Events, Season, Season Count, and Year. Your Power View Table shows the Season count by Season wise.

power-view-table

Filtering Table in Power View

By specifying the filter criteria, you may filter the data presented in the Table. In the Filters section, select the TABLE tab. 

selecting-table-tab

The season should be selected. To the right of Season Count, click the Advanced Filter mode icon. Choose Summer Season, then click Apply Filter. The Table will now only show summer records.

summer-records-shown

Next Article

Similar Reads