Reply
Member
Posts: 48
Registered: ‎10-04-2018
Accepted Solution

Calculating Avg # Activities Per Day/Week/Month

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!

 


Accepted Solutions
Community Support Team
Posts: 7,619
Registered: ‎05-02-2017

Re: Calculating Avg # Activities Per Day/Week/Month

Hi @win_toeknee,

 

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

Community Support Team _ Dale
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: 7,619
Registered: ‎05-02-2017

Re: Calculating Avg # Activities Per Day/Week/Month

Hi @win_toeknee,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 48
Registered: ‎10-04-2018

Re: Calculating Avg # Activities Per Day/Week/Month

@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....

Community Support Team
Posts: 7,619
Registered: ‎05-02-2017

Re: Calculating Avg # Activities Per Day/Week/Month

Hi @win_toeknee,

 

Please download the demo from here: https://1drv.ms/u/s!ArTqPk2pu-BkhBgYAX1_jsXde7IiPlease 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]
)

Calculating-Avg-Activities-Per-Day-Week-Month

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Member
Posts: 48
Registered: ‎10-04-2018

Re: Calculating Avg # Activities Per Day/Week/Month

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

 

So much gratitude! Thank you!