Pivot tables in Excel allow users to quickly summarize, analyze, and manipulate large data sets by organizing and comparing data points dynamically. They enable easy data exploration through drag-and-drop functionality, making it simple to generate reports and discover trends.
When used with Zebra BI add-ins, pivot tables can significantly enhance reporting in Excel. It’s important to fully utilize the options available. Zebra BI visuals can effectively read and display textual information if it is properly included in the data set. Below we’ll learn a really simple trick that will allow us to add a text field under the Values and display comments as text in a Pivot Table.
Preparing Dataset
Let’s start with a simple dataset that is organized in a two-level hierarchy. Ensure the table contains a “Comment” column you’d like to include in Zebra BI Tables later on.
Preparing a Pivot Table
Insert the Pivot Table:
- Click anywhere inside your original table.
- Go to Insert -> Pivot Table -> From Table/Range
Select Placement and Data Model:
- When the window opens, choose to place the pivot table in a new worksheet or the existing one.
- It is necessary to mark the “Add this data to the Data Model” option if we want to have the option to create additional measures in the model.
- A Pivot table has been placed in the Data model structure as shown below.
Creating a text measure
Next, we’ll create a formula for the Comment field, which cannot be placed under the Values area yet.
Add Measure:
- Right-click on the Table and select Add measure.
- A new window will open where you need to define a Measure name and a Formula.
Define Formula:
- Use the CONCATENATEX function, which returns a text string.
- This function requires two parameters: the “Table” and the “Column” from which you want to retrieve your text. For example:
=CONCATENATEX(Table_Inputs469;Table_Inputs469[Comment])
- The newly created measure will appear at the bottom of the table, starting with the “fx“.
Populating a Pivot table and use in Zebra BI
Add Fields to the Pivot Table:
- Start adding fields to the designated areas. Since we created a measure for comments, it can now be placed under the Values area.
Insert Zebra BI Tables Add-in:
- Go to Home -> Add-ins -> Zebra BI Tables.
- Navigate to the menu at the top right corner and click on the “Fields” icon to ensure all fields are correctly placed.
- Make sure the formula we created for comments is placed under the Comments placeholder.
By following these steps, you’ll have Zebra BI Tables with included dynamic comments, enhancing your reporting capabilities in Excel.
This guide has shown you how to include dynamic comments in your pivot tables, making your reports more informative and insightful. With these tools and techniques, you can explore and present your data in a more meaningful way, ultimately driving better decision-making and communication within your organization. Happy pivoting! 🔢