## Desktop

Highlighted
Member
Posts: 59
Registered: ‎11-26-2016

# Working out averages

Wondering if someone might help? I have monthly portfolio reports with unique identifiers for each underlying holding (ISINs). The ISIN column in my table is an alpha numeric column. I have used distinct count in a measure to calculate the number of ISINs for each month. I want to calculate the average number of ISINs held in the portfolio during the year. In 2015 I only have 8 months of data, in 2016/2017 I have 12 months and so far this year I have 10 months. How do I calculate the average annual number of ISINs in the portfolio?

The attached is a sample of the data

Accepted Solutions
Community Support Team
Posts: 5,693
Registered: ‎09-21-2016

## Re: Working out averages

Hi @Absalon29,

Please refer to below measures. Suppose the [Month&Year] column is a date column.

```CountofISIN = DISTINCTCOUNT('Sample Table'[ISIN])

count month number per year =
CALCULATE (
DISTINCTCOUNT ( 'Sample Table'[Month&Year] ),
FILTER (
ALL ( 'Sample Table' ),
'Sample Table'[Month&Year].[Year] = YEAR ( MAX ( 'Sample Table'[Month&Year] ) )
)
)

sum distinct count =
CALCULATE (
SUMX ( DISTINCT ( 'Sample Table' ), [CountofISIN] ),
FILTER (
ALL ( 'Sample Table' ),
'Sample Table'[Month&Year].[Year]
= YEAR ( SELECTEDVALUE ( 'Sample Table'[Month&Year] ) )
)
)

average per month = [sum distinct count]/[count month number per year] ```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

All Replies
Community Support Team
Posts: 5,693
Registered: ‎09-21-2016

## Re: Working out averages

Hi @Absalon29,

Please refer to below measures. Suppose the [Month&Year] column is a date column.

```CountofISIN = DISTINCTCOUNT('Sample Table'[ISIN])

count month number per year =
CALCULATE (
DISTINCTCOUNT ( 'Sample Table'[Month&Year] ),
FILTER (
ALL ( 'Sample Table' ),
'Sample Table'[Month&Year].[Year] = YEAR ( MAX ( 'Sample Table'[Month&Year] ) )
)
)

sum distinct count =
CALCULATE (
SUMX ( DISTINCT ( 'Sample Table' ), [CountofISIN] ),
FILTER (
ALL ( 'Sample Table' ),
'Sample Table'[Month&Year].[Year]
= YEAR ( SELECTEDVALUE ( 'Sample Table'[Month&Year] ) )
)
)

average per month = [sum distinct count]/[count month number per year] ```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.