cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

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

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

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

4 REPLIES 4
Microsoft
Microsoft

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

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

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

Highlighted
Anonymous
Not applicable

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

Microsoft
Microsoft

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

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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

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

 

So much gratitude! Thank you!

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors