How to sum a distinct count if for a cross table

[mcrea22]

I have a data set that looks like this:

Report Date  Unique Key            Responsible Person        Employee

11/10/2021   abcdef11102021    John J                                 Amy A

11/10/2021   cbcdef11102021  John J                                  Charlie C

11/11/2021   tbcdef11102021   Kim K                                   Tim T

11/12/2021   rbcdef11102021  Dan D                                    Rebecca R

The following statement  =IF ([Report Date] = [Max Date], COUNT(DISTINCT([Unique Key]),0)  is working normally to roll up all new instances by Employee, but I cannot seem to figure out how to Sum this count by [Responsible Person] in a cross table as a responsible person may have many subordinate employees. Any suggestions?  

[Digvijay_Singh]

What would be your expected output for the data set you have shared?

[Rockstar7]

@mcrea22  try this

Aggr(Sum(IF ([Report Date] = [Max Date], COUNT(DISTINCT([Unique Key]),0)),[Responsible Person])

[mcrea22]

No luck. This returns a null value for the responsible person unless I filter the chart for a specific person and date.

[Rockstar7]

@mcrea22  Can you please provide sample dataset and expected output 

[mcrea22]

If MaxDate = 11/10/2021 I  would expect to see the following result in my cross table:

where the employees and their unique keys are nested below their responsible persons 

Report Date      Responsible Person        CountofUniqueKeys

11/10/2021       John J                                   2

11/11/2021       Kim K                                     0

11/12/2021       Dan D                                     0