Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 

 

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous,

     

    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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    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