Aggregate monthly data into quarterly average

[Timmo1016]

Hello,

I am pretty new to powerBI. I am attempting to report on quarterly headcount data. My current table (below) is broken out monthly by regions (I am only able to add Region A here, but there are Regions B, C, and D. Can I somehow upload an excel file here? It does not accept xlxs or csv files). I want to be able to show this quarterly by region on a bar chart (Region A quarterly average for Q1 2022 should be 13394). 

I can't seem to figure out how to create this measure. I understand that the numbers in this data are going to always average out to whatever the last month in the quarter is, but this is just initial dummy data. Going forward, the monthly numbers will be changing. 

Ideally, I would want this number expressed in 1000s of employees. (I.E. Region A quartely average for 1000s of employees is 13.39) 

I was able to do this in tableau relatively easily, so I am sure it's possible in PBI. Any help would be appreciated! 

Date	Year	Month	Region	# of Employees
12/31/2021	2021	12	A	12906
1/31/2022	2022	1	A	13394
2/28/2022	2022	2	A	13394
3/31/2022	2022	3	A	13394
4/30/2022	2022	4	A	14018
5/31/2022	2022	5	A	14018
6/30/2022	2022	6	A	14018
7/31/2022	2022	7	A	14569
8/31/2022	2022	8	A	14569
9/30/2022	2022	9	A	14569
10/31/2022	2022	10	A	15216
11/30/2022	2022	11	A	15216
12/31/2022	2022	12	A	15216
1/31/2023	2023	1	A	15396
2/28/2023	2023	2	A	15396
3/31/2023	2023	3	A	15396
4/30/2023	2023	4	A	15522
5/31/2023	2023	5	A	15522
6/30/2023	2023	6	A	15522
7/31/2023	2023	7	A	15754
8/31/2023	2023	8	A	15754
9/30/2023	2023	9	A	15754
10/31/2023	2023	10	A	16083
11/30/2023	2023	11	A	16083
12/31/2023	2023	12	A	16083
1/31/2024	2024	1	A	16057
2/28/2024	2024	2	A	16057
3/31/2024	2024	3	A	16057
4/30/2024	2024	4	A	16057
5/31/2024	2024	5	A	16057
6/30/2024	2024	6	A	16727
7/31/2024	2024	7	A	16905
8/31/2024	2024	8	A	16905
9/30/2024	2024	9	A	17006
10/22/2024	2024	10	A	17006

[Selva-Salimi]

Hi @Timmo1016 

As you select the type of your date , in date format you can easily be able to show that column in date hierarchey of year-quarter. as you add this column in x-axis of bar chart, select date hierarchey and remove day and month. and dont forget to expant the visual .

you can add regions to the legend and for the y-axis you need to write a measure as follows:

measure _avg := var tbl=summarize ( your_table , date , #employees)

return averagex (tbl , #employee) /1000

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

[DataNinja777 · Accepted solution]

Hi @Timmo1016 ,

It's simple to produce the required output. First, create a calendar table and establish a relationship with your headcount fact table. Then, perform the average calculation as shown below:

I have attached an example pbix file for your reference.

Best regards,

[rajendraongole1]

Hi @Timmo1016 - I hope in you have seperate date table as like below:

create a calculated table as below:

DateTable = CALENDAR(MIN(Employ[Date]), MAX(Employ[Date]))

Create a DAX measure that calculates the quarterly average of employees for each region, and formats it in thousands

Quarterly Average =

DIVIDE(

    CALCULATE(AVERAGE(employ[# of Employees]),

    ALLEXCEPT(employ, employ[Region], DateTable[Year], DateTable[Quarter])),

    1000

)

Go to the Format tab and adjust the axis, data labels, and chart title to make your visualization clearer.You can also add tooltips that show additional details when hovering over the bars.

attached pbix file for your reference.