Share via

Pivot Table - Calculated Field - Counting Distinct Values For with If Condition Met - Excel 2013

Anonymous
2015-04-28T22:12:33+00:00

How can I create a calculated field in a pivot table that will count distinct values of Field1, given that Field2 meets a particular condition?

My limitations:

Needs to be in a pivot table

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 Pivot Table output (a subset of it...there would be other fields, like sums) 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 combo of SUM, IF, FREQUENCY, and MATCH) for non-pivot table data. Can I do this with Pivot table fields?

I don't have any VBA background.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments
Answer accepted by question author
  1. Anonymous
    2015-04-30T21:06:40+00:00

    Hi,

    The PowerPivot has a DISTINCTCOUNT function.  After dragging the Date Period field to the Row labels and Field2 to the column labels, enter the following calculated field formula

    =DISTINCTCOUNT(data[Client ID])

    Data is the name given to the original dataset.

    Hope this helps.

    Thanks! I didn't think (or know) to use a PowerPivot table. 

    I actually created a calculated field with the following formula

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

    Appreciate your help on this!

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2015-05-01T05:33:39+00:00

    You are welcome.

    0 comments No comments
  2. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2015-04-28T23:52:30+00:00

    Hi,

    The PowerPivot has a DISTINCTCOUNT function.  After dragging the Date Period field to the Row labels and Field2 to the column labels, enter the following calculated field formula

    =DISTINCTCOUNT(data[Client ID])

    Data is the name given to the original dataset.

    Hope this helps.

    0 comments No comments