Formula to calculate how many distinct persons responded with a specific answer across tables?

[HeadAgainstABrickWall]

Hi all, I have a body of data with individuals providing information about specific work tasks.

Forgive formatting, on phone, but my data is:

Date | Person | Task | Information.

In my analysis sheet, I have:

Task | Count entries | Count contributors | Sum information.

What I want to show is how many distinct individuals provided information about a task during the study period (not unique, as contributors could provide information about tasks on multiple days).

I have tried SUM(1/COUNTIFS(DataTable[Person], DataTable[Person], DataTable[Task], AnalysisTable[Task])) but that's only worked in the DataTable with a single task type - all else show #DIV/0!

Any help welcome!

 

[jasonb75]

I can't test at the moment, from memory it should be something like

=SUM(IFERROR(1/COUNTIFS(DataTable[Person], DataTable[Person], DataTable[Task], @AnalysisTable[Task]),0))

 

[HeadAgainstABrickWall]

I can't test at the moment, from memory it should be something like

=SUM(IFERROR(1/COUNTIFS(DataTable[Person], DataTable[Person], DataTable[Task], @AnalysisTable[Task]),0))

Unfortunately that just returns 0 instead of #DIV/0!, which would be fine except I know that the result shouldnt be 0 as there is matching data in the respective tables.

 

[jasonb75]

Are there empty cells in the columns that the formula refers to?

 

[HeadAgainstABrickWall]

Are there empty cells in the columns that the formula refers to?

Nope, none.

 

[jasonb75]

If this doesn't when then i would have to guess that there is no exact match.

=SUM(IFERROR(1/COUNTIFS(DataTable[Person], DataTable[Person], DataTable[Task],DataTable[Task],DataTable[Task],@AnalysisTable[Task]),0))

 

[HeadAgainstABrickWall]

If this doesn't when then i would have to guess that there is no exact match.

=SUM(IFERROR(1/COUNTIFS(DataTable[Person], DataTable[Person], DataTable[Task],DataTable[Task],DataTable[Task],@AnalysisTable[Task]),0))

Definite matches, a bog standard COUNTIF/COUNTIFS works.

I'll keep hacking away at it. First time I've ever known how to do something in PowerBI but not in Excel!!

 

[jasonb75]

I just checked my suggestion again and noticed that i had the @ symbol in the wrong place.

I've assumed that the formula is going into the analysis table. If not then you may encounter relative reference problems.

=SUM(IFERROR(1/COUNTIFS(DataTable[Person], DataTable[Person], DataTable[Task],DataTable[Task],DataTable[Task],[@Task]),0))

 

[HeadAgainstABrickWall]

I just checked my suggestion again and noticed that i had the @ symbol in the wrong place.

I've assumed that the formula is going into the analysis table. If not then you may encounter relative reference problems.

=SUM(IFERROR(1/COUNTIFS(DataTable[Person], DataTable[Person], DataTable[Task],DataTable[Task],DataTable[Task],[@Task]),0))

I will give this a go at work tomorrow and let you know! It is in the analysis table.

Thanks very much for taking the time to reply ??

 

[HeadAgainstABrickWall]

T

I just checked my suggestion again and noticed that i had the @ symbol in the wrong place.

I've assumed that the formula is going into the analysis table. If not then you may encounter relative reference problems.

=SUM(IFERROR(1/COUNTIFS(DataTable[Person], DataTable[Person], DataTable[Task],DataTable[Task],DataTable[Task],[@Task]),0))

That worked great! Cheers!