How to get an average row count for a grouping across multiple queries

[allauthors]

I'm trying to figure out how to get the average row count per "item" for a particular status.  Hoping someone can help
Consider the following simplified example tables:

Items table

ItemId	Status
1	QA
2	Done
1	QA
2	WIP
2	QA
3	WIP
3	Done

PeoplesItems table

Person	ItemId	Another Measurement
Robert	1	6
Robert	2	3
Jenny	3	2

I'm looking to produce a visualization which lists people and shows their Average number of times in QA per Item along with other calculations I've already figured out how to do.

Person	QA's Per Item	Sum Another Measurement
Robert	1.5	9
Jenny	0	2

Assuming that People to Item also has many other measurements that are also a part of the visualizaion

How do I create a measurement that will let me produce the result above for QA's Per Item.  Most of my DAX has been limited to counting rows where another column is a particular value.   I'm guessing there's some kind of DAX that can produce this average, but I'm stumped.  So if anyone can suggest the right DAX and the right table to put the measure on I'd be super appreciative.

I"m particularly keen for a solution that explains the "why" of the answer so that I can solve similar things in the future on my own.

[v-xiandat-msft · Accepted solution]

Hi @allauthors ,

Below is my table1:

Below is my table2:

The following DAX might work for you:

Measure = 
   var iem = DISTINCTCOUNT('Table'[Itemid])
   var sta = 
   CALCULATE(
    COUNTROWS('Table'),
    FILTER('Table','Table'[Status]="QA" )
   )
    RETURN
      sta/iem

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.