Calculating Average of distinct count by category

[anwarbi]

Hi,

I am looking to calculate average of distinct count of respondents by Peers.

Here is the dummy data, I have two tables in power bi.

Table 1 is a master table with all the data fields and table 2 only have 'Peers' name which I have created the relation with 'partner' field in table 1.

I am looking to create a measure that calculates a distinct count of respondent ID (from table 1) for the peers (in table 2, please note that the peers should be static and not affected by any filters in the report.

And then calculate the average of distinct count of respondents for each peer.

Output:

The calculated measure should give the following output e.g.  Peer (A) has 2 unique responses, Peer (B) has 3 unique responses. So the average will be 2.5 for both peers.   

I hope this gives enough data points. Thanks. 

Table 1

partner	Respondent ID
A	A1212
B	B1212
A	A1212
D	C1212
E	B1212
B	A1212
A	B1212
H	D1212
B	B1212
B	D1212

Table 2

Peers

A

B

Thanks.

[amitchandak]

@anwarbi , Create measure like

averageX(Values(Table2[Peers]), calculate(distinctcount(Table1[Respondent ID])))

[anwarbi]

Thanks Amit for your help. While the measure is correct, it is not giving me the desired result.

Currently, this measure calculates the output for all the Partners in table 1, however, I only want the output based on the Peers (i.e. just for A&B) in table 2.  

Please note that i do not want to have a drop-down filter to select the desired peers. the calculation should be restrcited to Peers in table 2.

[v-janeyg-msft · Accepted solution]

Hi, @anwarbi 

You can use peers field and the measure in visual, then filter out other partners in filter pane by using advanced filtering:value is not blank.

Like this:

meassure =
AVERAGEX (
    VALUES ( Table2[Peers] ),
    CALCULATE ( DISTINCTCOUNT ( Table1[Respondent ID] ) )
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If problem still persists, pls let me know.

Best Regards,
Community Support Team _ Janey