Calculating Avg # Activities Per Day/Week/Month

[Anonymous]

I am trying to calculate the average number of activities per day / week / month. Current data has an activities column [actv_id]. The below syntax worked fine for calculating the average per day. 

Avg # of Activities Per Day =
AVERAGEX(
CALCULATETABLE('Customer Support Activity - CSR', 'Customer Support Activity - CSR'[ToDate] <> BLANK() ),
VAR St = 'Customer Support Activity - CSR'[fr_date]
VAR En = 'Customer Support Activity - CSR'[ToDate]
RETURN
COUNTROWS(
CALCULATETABLE(
'Calendar',
'Calendar'[Date] >= St,
'Calendar'[Date] <= En
)
)
)

There are calculated columns for:

- ToDate = TODAY()

- Week = WEEKNUM('Customer Support Activity  - CSR'[fr_date])

- Month = FORMAT('Customer Support Activity  - CSR'[fr_date], "MMM yyyy")

Problem: I assumed that if I changed "VAR En" = [Week], it would work for week (same as if I was to change it month) but that did not work. 

Goal: I am trying to create a measure where I can calculate Avg # of Activities per Week + per Month. 

Any assistance on how I can make this work is greatly appreciated!

Thank you Datanauts!

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Can you share a sample file, please? You can try a formula like below.

Measure =
AVERAGEX (
    SUMMARIZE (
        FactSales,
        DimDate[CalendarWeek],
        "#activities", COUNTROWS ( FactSales )
    ),
    [#activities]
)
    + AVERAGEX (
        SUMMARIZE (
            FactSales,
            DimDate[CalendarMonth],
            "#activities", COUNTROWS ( FactSales )
        ),
        [#activities]
    )

Best Regards,
Dale

[Anonymous]

@v-jiascu-msft Thank you so much for your response. I am trying workup the syntax that you have provided. For reference, please see my active workbook. I am curious to know your thoughts on how to achieve the anticipated goal of calculating the average # of activities per week + average # of activities per month. 

Below is the link to my active workbook. 

I appreciate you and gratitude!

Anthony

https://github.com/anthonynguyen3/Power-BI/blob/master/Customer%20Support%20Activity%20-%20CSR%20BI....

[v-jiascu-msft]

Hi @Anonymous,

Please download the demo from here: https://1drv.ms/u/s!ArTqPk2pu-BkhBgYAX1_jsXde7Ii. Please don't share sensitive data. 

I'm not 100 percent sure what the result should be. If it's the average value monthly, you just need one measure. If it's a value of the average of the values of months, there could be three measures. 

1. Create a new column due to the old relationship doesn't match any values.

DateForRelationship = [fr_date].[Date]  //delete the relationship first.

2. Create three measures, including one renewed measure.

Avg # of Activities Per Day New =
AVERAGEX (
    'Customer Support Activity  - CSR',
    DATEDIFF ( [fr_date], TODAY (), DAY )
)

Avg # of Activities Per Week New =
AVERAGEX (
    SUMMARIZE (
        'Customer Support Activity  - CSR',
        'Calendar'[Week],
        "WeeklyTotal", CALCULATE (
            SUMX (
                'Customer Support Activity  - CSR',
                DATEDIFF ( [fr_date], TODAY (), DAY )
            )
        )
    ),
    [WeeklyTotal]
)

Avg # of Activities Per Month =
AVERAGEX (
    SUMMARIZE (
        'Customer Support Activity  - CSR',
        'Calendar'[Month],
        "MonthlyTotal", CALCULATE (
            SUMX (
                'Customer Support Activity  - CSR',
                DATEDIFF ( [fr_date], TODAY (), DAY )
            )
        )
    ),
    [MonthlyTotal]
)

Best Regards,
Dale

[Anonymous]

@v-jiascu-msft You my friend, are a hero!

So much gratitude! Thank you!