Forum Discussion

mariajuliao's avatar
mariajuliao
Frequent Visitor
3 years ago

Monthly Average Distinct Count - Measure

How can I create a measure that calculates the  monthly average of distinct count

CaseNumberCreatedDateComputer
abcde1/2/2023Computer1
abcdf1/3/2023Computer1
abcdg1/4/2023Computer2
abcdh2/14/2023Computer2
abcdi2/17/2023Computer1

 

In this example, for month 1 I have three cases. For month 2 I have two cases. The monthly average is 2.5 cases.

How do I create a measure to calculate this? 

Additionally, how do I create a measure to calculate the average per month, per computer?

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi mariajuliao 

    try like:

    Measure =
    VAR _table=
    ADDCOLUMNS(
         TableName,
         "YYYYMM",
         FORMAT(TableName[CreatedDate], "YYYYMM")
    )
    RETURN
    AVERAGEX(
        SUMMARIZE(_table, [YYYYMM]),
        CACULATE(COUNTROWS(_table))
    )