Reply
Highlighted
Member
Posts: 59
Registered: ‎11-26-2016
Accepted Solution

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 

 

Average Number of ISINs.PNG

 


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.

View solution in original post


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.