How to Extract Low Transmission Slots from Excel PivotTable Data
Question details
The user needs to identify low transmission values based on RNTI and slot data, mark these specific slots with an 'x' in the result table, and calculate the percentage loss.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing data transmission logs to pinpoint underperforming slots by dynamically grouping them by RNTI.
- Observed behavior
- The goal is to dynamically highlight or extract low transmission slots using formulas, conditional formatting, or Power Pivot measures in a PivotTable.
Ensure your raw data is formatted as an official Excel Table (Ctrl+T) and verify that the Power Pivot add-in is enabled if you plan to use DAX measures.
Use Power Pivot and DAX Measures to Flag Slots
Power Pivot handles large datasets efficiently and allows you to create dynamic measures to flag low transmission slots based on a calculated maximum threshold.
By adding your data to the Data Model, you can define custom DAX measures to calculate row counts and compare them dynamically against the maximum row count for a given RNTI.
Select your data table, navigate to Insert > PivotTable, and ensure the 'Add this data to the Data Model' checkbox is checked before clicking OK.
Open the Power Pivot tab, click 'Measures' > 'New Measure', and create a base count measure using the DAX formula: Count := COUNTROWS(Table1).
Create a second measure to flag low transmission. Use DAX to calculate the max count for the RNTI, then return 'x' if the slot count is below your threshold (e.g., 70% of max). Example DAX variables: VAR maxInRow = CALCULATE(MAXX(VALUES(Table1[Slot]), [Count]), ALLEXCEPT(Table1, Table1[Rnti])).
In your new PivotTable, drag RNTI to the Rows area, Slot to the Columns area, and add your new 'Mark' measure to the Values area to display the 'x' flags.

Use Dynamic Array Functions (Microsoft 365)
For users with the latest Excel versions, dynamic array functions can generate the required data matrix directly without needing the Data Model.
Analyze PivotTable Data Effortlessly with WPS Spreadsheet
WPS Spreadsheet offers robust tools for grouping data, creating PivotTables, and applying advanced formulas to analyze data transmission logs efficiently without complex setups.
- 1. Open Your Data: Launch WPS Spreadsheet and open your raw transmission data file.
- 2. Insert a PivotTable: Select your entire data range, navigate to the Insert tab, and click 'PivotTable'.
- 3. Group Data by RNTI: Drag the RNTI field to the Rows area and the Slot field to the Columns area to structure your matrix.
- 4. Highlight Low Values: Use WPS Spreadsheet's built-in Conditional Formatting under the Home tab to automatically highlight transmission values falling below your target threshold.

Frequently Asked Questions
What is the best way to define a threshold for low transmission in Excel?
You can define a threshold dynamically using statistical functions like AVERAGE, MEDIAN, or PERCENTILE. Alternatively, using DAX in Power Pivot allows you to calculate a specific percentage of the maximum transmission value for each individual RNTI.
Why is the PIVOTBY function returning an error in my Excel?
The PIVOTBY function is a dynamic array function available only in the latest Microsoft 365 versions of Excel. If you are using an older version, the function will not be recognized. You should use Power Pivot or traditional PivotTables with helper columns instead.
How do I add my data to the Data Model for Power Pivot?
When creating a PivotTable from the Insert tab, look at the bottom of the 'Create PivotTable' dialog window and check the box that says 'Add this data to the Data Model'. This step is required to write custom DAX measures.




