Average of sum per month, per person

[JPraidkee]

Hello,

I am very new to Power BI, and need some help.

I would like to calculate the Average Number of Tasks Completed, Per Month, per Person.

I would also like this figure to automatically update as we progress throughout the year.

for example, today will calculate the avg number of tasks per person from Jan - June; in October, the visual will automatically update to calculate the average number of tasks per person from Jan - Oct.  (I plan to display this in a Card and as a line on a bar chart).

Thanks in advance!

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[JPraidkee]

Sales Rep	Task Completed Date
Amy	1/13/2024
Erin	1/29/2024
Mike	2/6/2024
Erin	2/8/2024
Carl	3/3/2024
Mike	3/30/2024
Carl	4/2/2024
Erin	4/6/2024
Carl	4/10/2024
Carl	4/24/2024
Mike	5/2/2024
Erin	5/3/2024
Erin	5/3/2024
Carl	5/3/2024
Erin	5/9/2024
Mike	5/14/2024
Amy	5/29/2024
Erin	6/1/2024
Amy	6/1/2024
Mike	6/3/2024

20 tasks completed.  Average tasks /month/ rep is .83.

[Anonymous · Accepted solution]

Thanks @Ashish_Mathur 

Hi, @JPraidkee 

Based on your description, I used the sample data you provided:

I used the following DAX expression:

MEASURE =
VAR _rep =
    DISTINCTCOUNT ( 'Table'[ales Rep] ) // Count how many reps there are
VAR _table =
    SUMMARIZE (
        'Table',
        'Table'[Task Completed Date],
        "Month", MONTH ( 'Table'[Task Completed Date] )
    ) // Generate a dummy table for the date column and extract the corresponding month
VAR _month =
    COUNTROWS ( SUMMARIZE ( _table, [Month] ) ) // How many months are counted
VAR _count_completed_task =
    COUNTROWS ( 'Table' )
RETURN
    DIVIDE ( DIVIDE ( _count_completed_task, _month ), _rep )

The expected result you gave was obtained at 0.83:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

How did you calculate the 0.83?