Here's how to create PivotTables with calculated KPIs in Excel:

1. Prepare your data:

* Ensure your data is organized in a tabular format with clear column headers. This will be the source data for your PivotTable.
* Identify the dimensions (categories) and measures (values) you want to analyze.

2. Create a PivotTable:

* Select the data range you want to analyze.
* Go to the "Insert" tab and click "PivotTable."
* Choose where you want to place the PivotTable (new worksheet or existing one).

3. Build the PivotTable:

* Drag and drop fields from the "Field List" to the "Rows," "Columns," and "Values" areas.
* Rows: These represent categories you want to group your data by (e.g., product, region, date).
* Columns: These represent additional categories for further breakdown (e.g., year, salesperson).
* Values: These are the numerical data points you want to summarize (e.g., sales amount, average price).

4. Create Calculated Fields for KPIs:

* Go to the "Analyze" tab (only visible when a PivotTable is selected).
* Click on "Fields, Items, & Sets" and then choose "Calculated Field."
* In the "Formula Name" box, enter a descriptive name for your KPI (e.g., "Sales Growth").
* In the "Formula" box, write the calculation using DAX formulas (Data Analysis Expressions). Here are some examples:
* Percentage Change: `(Current Period - Previous Period) / Previous Period`
* Target Achievement: `(Actual Sales / Sales Target) * 100`
* Average Order Value: `Total Sales / Number of Orders`
* Click "OK" to save your calculated field.

5. Add KPIs to the Values Area:

* Drag and drop your newly created calculated field from the "Field List" to the "Values" area.
* You can format the KPI values for better presentation (e.g., percentages, color coding).

6. Analyze and Customize:

* Use slicers and filters to analyze your data further based on specific categories.
* You can create multiple calculated fields for various KPIs and add them to the "Values" area for a comprehensive view.
* Format the PivotTable layout and customize the appearance for clear communication of your insights.

Tips:

* Use clear and concise names for your calculated fields and KPIs.
* Consider using conditional formatting to highlight positive or negative KPI values.
* You can create PivotCharts to visually represent your KPIs alongside the PivotTable data.

By following these steps, you can create informative PivotTables with calculated KPIs, allowing you to effectively analyze and track your performance metrics within Excel.