Count distinct values subject to condition in Calculated Field

Question

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.

Answer 1

I suggest creating a PivotTable with Add this data to the Data Model checked, Date Period for ROWS, Field 2 for COLUMNS and Distinct Count of Client ID for VALUES.

I appreciate this might count as "simply a field change setting".

Answer 2

After some playing around, I think I have figured it out:

• Use a PowerPivot table
• Use calculated field
• Use the CALCULATE function in the calculated field to do a distinct count given a filter/condition

You install the PowerPivot add-on (File -> Options -> Add-Ins -> Managed: COM Add-ins -> Check Microsoft Office PowerPivot for Excel 2013) to get a PowerPivot tab.

You would need to add the data to the data model (by clicking Manage and import the data, or highlight the table and click Add to Data Model). Then, you click the PivotTable button in the PowerPivot manage area.

Drag the Date Period to the Rows. Then, in the PowerPivot tab, click Calculated Fields -> New Calculated Field.

Now, my field (named: Distinct Client Count Where Field2 = 1) has the following formula:

=CALCULATE(DISTINCTCOUNT(Table1[Client ID]),Table1[Field 2] = 1)

Where Table1 is the name of my data model (source data). CALCULATE function allows you to apply filters to the aggregate calculation. DISTINCTCOUNT is a distinct count of the first field you put in.

Here is the final output:

╔═════════════╦════════════════════════════════════════╗
║ Date Period ║ Distinct Client Count Where Field2 = 1 ║
╠═════════════╬════════════════════════════════════════╣
║ 1           ║                                      3 ║
║ 2           ║                                      1 ║
║ Grand Total ║                                      3 ║
╚═════════════╩════════════════════════════════════════╝