How can I create a Calculated Field in a PivotTable that will count distinct values of Field1, given that Field2 meets a particular condition? My limitations:
- Needs to be in a PivotTable
- Needs to be a Calculated Field
- The Calculated Field needs to be a unique count "formula", not simply a field setting change.
Here is an example of what I am trying to achieve. Raw data:
╔═════════════╦═══════════╦═════════╗
║ Date Period ║ Client ID ║ Field 2 ║
╠═════════════╬═══════════╬═════════╣
║ 1 ║ A ║ 1 ║
║ 1 ║ A ║ 1 ║
║ 1 ║ A ║ 1 ║
║ 1 ║ B ║ 1 ║
║ 1 ║ B ║ 1 ║
║ 1 ║ C ║ 1 ║
║ 2 ║ A ║ 1 ║
║ 2 ║ A ║ 1 ║
║ 2 ║ B ║ 0 ║
║ 2 ║ C ║ 0 ║
║ 2 ║ C ║ 0 ║
╚═════════════╩═══════════╩═════════╝
Here is what the PivotTable output would look like:
╔═════════════╦═══════════════════════════════════╦═══════════════════════════════════╗
║ Date Period ║ Distinct Clients where Field 2= 1 ║ Distinct Clients where Field 2= 0 ║
╠═════════════╬═══════════════════════════════════╬═══════════════════════════════════╣
║ 1 ║ 3 ║ 0 ║
║ 2 ║ 1 ║ 2 ║
╚═════════════╩═══════════════════════════════════╩═══════════════════════════════════╝
I have seen methods of using array functions (a combination of SUM, IF, FREQUENCY, and MATCH) for non-pivot table data. Can I do this with PivotTable fields?
I don't have any VBA background. Using Excel 2013.
Best Answer
I suggest creating a PivotTable with Add this data to the Data Model checked,
Date Periodfor ROWS,Field 2for COLUMNS and Distinct Count ofClient IDfor VALUES.I appreciate this might count as "simply a field change setting".