Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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!

 

Need Help
Tips and Tricks
Tutorial Requests
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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

4 Replies

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

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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

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

        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]
)

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

         

        Best Regards,
        Dale