Calculate Average of distinct count of values by filtered column

[anwarbi]

I am looking to calculate average of distinct count of responses by location (locations need to be filtered lets say location A, Location B and Location C

I am not looking to aggregare the filtered locations but want to have the distict count of responses for each location and then have an average of each category. Any help would be appreciated. 

[v-angzheng-msft]

Hi, @anwarbi 

@Nathaniel_C 's solution seems to work, but it looks a bit complicated for me.
I came up with another method that might be helpful.

distinct count =
CALCULATE (
    DISTINCTCOUNT ( Table1[Respondent ID] ),
    FILTER ( ALL ( 'Table1' ), [partner] = MAX ( 'Table2'[Peers] ) )
)

average of disctinct count =
AVERAGEX ( ALL ( Table2 ), [distinct count] )

Result:

I tried to use just one measure to get the result, but I'm not successful yet, maybe someone can get it

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Nathaniel_C]

Hi @anwarbi ,
Next I created  2 columns in Table1, then a table and then a measure to give the average value. I am working on possibly a better solution, but this works.

Distinct Count col =
VAR _getPartner =
    MAX ( Table1[Partner] )
VAR _calc =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Respondent ID] ),
        FILTER ( ALL ( Table1[Respondent ID] ), MAX ( Table1[Partner] ) = _getPartner )
    )
RETURN
    _calc

If = IF(Table1[Partner] in VALUES(Table2[Peers]), 1,0)

Average Value = 
var _peers = [Number of Peers]

var _sumMax = SUM('Summarize Table1'[Max]  )

return DIVIDE(_sumMax,_peers)

Summarize Table1 =
SUMMARIZECOLUMNS (
    Table1[Partner],
    FILTER ( Table1, Table1[If] = 1 ),
    "Max", MAX ( Table1[Distinct Count col] )
)

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[anwarbi]

Hi @Nathaniel_C 

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 disctinct count of respondent ID (from table 1) for the peers (in table 2) .

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

e.g. the output should be: Peer (A) has 2 unique responses, Peer (B) has three unique responses.

so the average will be 2.5 for both peers.   

I hope this gives enough, data points to calculate. 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

[Nathaniel_C]

Hi @anwarbi ,
Thank you for the set up data. Below you will find a measure that can either be added to Table 2 or to Table 1 to give you the distinct count of the locations which may be attributed to the Peers in Table 2. Will get you the rest shortly. Because you linked the two tables, we can drop the Peers column from Table 2 onto the visual with Table 1 to filter it.

Please let me know if this is proceeding on the right path. 

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Distinct Count =
VAR _getPartner =
    MAX ( Table1[Partner] )
VAR _calc =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Respondent ID] ),
        FILTER ( ALL ( Table1[Respondent ID] ), MAX ( Table1[Partner] ) = _getPartner )
    )
RETURN
    _calc

[Nathaniel_C · Accepted solution]

Hi @anwarbi ,

Here it is without building a separate table.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Average Value 2 =
VAR _peers = [Number of Peers]
VAR _summarizeTable12 =
    SUMMARIZECOLUMNS (
        Table1[Partner],
        FILTER ( Table1, Table1[If] = 1 ),
        "Max", MAX ( Table1[Distinct Count col] )
    )
VAR _sumMax =
    GROUPBY (
        _summarizeTable12,
        Table1[Partner],
        "check", SUMX ( CURRENTGROUP (), [Max] )
    )
VAR _TEST =
    SUMX ( _sumMax, [check] )
RETURN
    DIVIDE ( _TEST, _peers )

[Nathaniel_C]

Hi @anwarbi ,
Please give us more information to be able to help you.  Perhaps a mocked up table done in Excel, or a pbix with a table.
If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you. 

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Thank you,
Nathaniel