Calculating Average of Distinct count of values using slicer variable

[anwarbi]

Hi,

Newbie here, I am trying to calculate average by category, based on the ALLSelected values in slicer.

In the below measure, I am considering Variable as all the selected company names in the slicer, then for those companies, I am calculating distinctcount of email_ID and then averagex of each companies.

However, the output i am getting is 1, which is incorrect.

I am using the below measure:

Peer group distinct count =

VAR SelectedPeers = ALLSELECTED('Peer Group'[Company])

return

    AVERAGEX(

    FILTER('raw_data','raw_data'[Company] IN SelectedPeers),

    CALCULATE(DISTINCTCOUNT('raw_data'[email_id]))

)

Please note that Peer Group'[Company] in the above measure is a value from table created using the values from  raw_data'[Company].  

Below is the sample table and the expected output:

Company name	Distinct count of email id
Company A	35
Company B	40
Company C	25
Company D	17
Company E	37
Company F	24

So for example, If i select Company A, Company B and Company C in the slicer I should get an average output of 33 (which is an average of 35,40 and 25).  

Appreciate if you are able to help me solve this. 

Best,

Anwar

[TomMartens]

Hey @anwarbi ,

please create a pbix file containing sample data that still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix to onedrive, Google Drive, or Dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method, share the xlsx as well.

Do not forget to create a table that contains the expected result based on given filters.

Regards,

Tom

[anwarbi]

Thanks Tom for your response. I will try to upload the data, if it helps. Thank you!